-- ============================================================================
-- ALZAR FZE — Shared MySQL database (website + CRM)
-- Import via phpMyAdmin:  Import → choose this file → Go
-- Or CLI:  mysql -u <user> -p < database/alzar_mysql_schema.sql
-- ----------------------------------------------------------------------------
-- This ONE database is shared by the public website (reads published content)
-- and the CRM (creates/approves content). Approval gate = content_items.status.
-- Character set utf8mb4 so Arabic (and emoji) store correctly.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS `alzar_website`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `alzar_website`;

-- ---------------------------------------------------------------- users ----
-- Admin/staff accounts. Shared login with the CRM.
--   role: 'admin'  = can APPROVE content (you)
--         'editor' = can create/submit content (employees)
--         'user'   = basic
CREATE TABLE IF NOT EXISTS `users` (
  `id`             int AUTO_INCREMENT PRIMARY KEY,
  `email`          varchar(320) NOT NULL UNIQUE,
  `password_hash`  text,
  `name`           text,
  `login_method`   varchar(64) DEFAULT 'password',
  `role`           enum('user','editor','admin') NOT NULL DEFAULT 'user',
  `created_at`     timestamp NOT NULL DEFAULT (now()),
  `updated_at`     timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  `last_signed_in` timestamp NOT NULL DEFAULT (now())
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------ contact_submissions ------
CREATE TABLE IF NOT EXISTS `contact_submissions` (
  `id`          int AUTO_INCREMENT PRIMARY KEY,
  `name`        varchar(255) NOT NULL,
  `email`       varchar(320) NOT NULL,
  `company`     varchar(255),
  `industry`    varchar(100),
  `message`     text NOT NULL,
  `status`      enum('new','read','replied','archived') NOT NULL DEFAULT 'new',
  `created_at`  timestamp NOT NULL DEFAULT (now()),
  `updated_at`  timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_submissions_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------- content_items -------
-- Articles, products, principals, job postings — all content types.
-- The WEBSITE displays ONLY rows where status='published'.
-- The CRM moves items: draft → pending_review → approved → published/rejected.
CREATE TABLE IF NOT EXISTS `content_items` (
  `id`               int AUTO_INCREMENT PRIMARY KEY,
  `type`             enum('article','product','principal','job') NOT NULL,
  `slug`             varchar(255) NOT NULL,
  `status`           enum('draft','pending_review','approved','published','rejected')
                       NOT NULL DEFAULT 'draft',
  `title_en`         varchar(512) NOT NULL,
  `title_ar`         varchar(512),
  `body_en`          text,
  `body_ar`          text,
  `excerpt_en`       varchar(1024),
  `excerpt_ar`       varchar(1024),
  `meta_title`       varchar(512),
  `meta_description` varchar(1024),
  `og_image`         varchar(1024),
  `canonical_url`    varchar(1024),
  `hero_image`       varchar(1024),
  `data_json`        text,
  `created_by`       int,
  `reviewed_by`      int,
  `review_note`      text,
  `created_at`       timestamp NOT NULL DEFAULT (now()),
  `updated_at`       timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  `published_at`     timestamp NULL,
  INDEX `idx_content_status_type` (`status`, `type`),
  INDEX `idx_content_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ content_audit ------
-- Immutable log of every content change (who did what, when). Owner protection.
CREATE TABLE IF NOT EXISTS `content_audit` (
  `id`         int AUTO_INCREMENT PRIMARY KEY,
  `content_id` int NOT NULL,
  `action`     varchar(64) NOT NULL,
  `actor_id`   int,
  `note`       text,
  `created_at` timestamp NOT NULL DEFAULT (now()),
  INDEX `idx_audit_content` (`content_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- After import: create your admin login (run from the app, NOT in SQL, so the
-- password is properly hashed):
--   pnpm tsx scripts/create-admin.ts you@alzarfze.com "STRONG_PASSWORD" "Your Name"
-- ============================================================================