-- ═══════════════════════════════════════════════════════════════════════════
-- Debrieft — Complete Database Schema (Fresh Install)
-- MySQL / MariaDB — run this ONCE on a new database.
-- Do NOT run on an existing database — use ALTER migrations instead.
-- ═══════════════════════════════════════════════════════════════════════════

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

-- ── Projects ──────────────────────────────────────────────────────────────────
CREATE TABLE projects (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    client_id  INT NOT NULL,
    title      VARCHAR(200) NOT NULL,
    status     ENUM('intake','brief','quote','contract','active','handover','completed') DEFAULT 'intake',
    slug       VARCHAR(64) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Intake Forms ──────────────────────────────────────────────────────────────
CREATE TABLE intake_forms (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    project_id        INT NOT NULL,
    business_name     VARCHAR(200),
    industry          VARCHAR(100),
    website_url       VARCHAR(255),
    project_type      VARCHAR(100),
    goals             TEXT,
    target_audience   TEXT,
    competitors       TEXT,
    design_style      VARCHAR(100),
    color_preferences TEXT,
    features_needed   TEXT,
    budget_range      VARCHAR(50),
    timeline          VARCHAR(100),
    additional_notes  TEXT,
    submitted_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AI Briefs ─────────────────────────────────────────────────────────────────
CREATE TABLE briefs (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    project_id   INT NOT NULL,
    content      LONGTEXT,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_at    TIMESTAMP NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Quotes ────────────────────────────────────────────────────────────────────
CREATE TABLE quotes (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    project_id   INT NOT NULL,
    quote_number VARCHAR(30),
    valid_until  DATE,
    notes        TEXT,
    discount     DECIMAL(10,2) DEFAULT 0,
    tax_percent  DECIMAL(5,2)  DEFAULT 0,
    status       ENUM('draft','sent','accepted','declined') DEFAULT 'draft',
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Quote Line Items ──────────────────────────────────────────────────────────
CREATE TABLE quote_items (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    quote_id    INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity    DECIMAL(10,2) DEFAULT 1,
    unit_price  DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contracts ─────────────────────────────────────────────────────────────────
CREATE TABLE contracts (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    project_id         INT NOT NULL,
    title              VARCHAR(200),
    content            LONGTEXT,
    status             ENUM('draft','sent','signed') DEFAULT 'draft',
    designer_signature MEDIUMTEXT,
    client_signature   MEDIUMTEXT,
    signed_at          TIMESTAMP NULL,
    sent_at            TIMESTAMP NULL,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Handover Items ────────────────────────────────────────────────────────────
CREATE TABLE handover_items (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    project_id   INT NOT NULL,
    category     ENUM('credentials','files','instructions','links','notes') DEFAULT 'notes',
    title        VARCHAR(200) NOT NULL,
    content      TEXT,
    is_sensitive TINYINT(1) DEFAULT 0,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Invoices ──────────────────────────────────────────────────────────────────
CREATE TABLE invoices (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    user_id        INT NOT NULL,
    project_id     INT NOT NULL,
    quote_id       INT DEFAULT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    currency       ENUM('USD','EUR','GBP') DEFAULT 'USD',
    issued_at      DATE NOT NULL,
    due_date       DATE DEFAULT NULL,
    notes          TEXT DEFAULT '',
    status         ENUM('draft','sent','paid') DEFAULT 'draft',
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (quote_id)   REFERENCES quotes(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Website Specs ─────────────────────────────────────────────────────────────
CREATE TABLE website_specs (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    project_id       INT NOT NULL,
    user_id          INT NOT NULL,
    -- Step 1: Basic Info
    business_name    VARCHAR(200) DEFAULT '',
    tagline          VARCHAR(255) DEFAULT '',
    about            TEXT,
    industry         VARCHAR(100) DEFAULT '',
    website_url      VARCHAR(255) DEFAULT '',
    -- Step 2: Pages
    pages            TEXT COMMENT 'JSON array of page names + descriptions',
    -- Step 3: Services
    services         TEXT COMMENT 'JSON array of service name + description + price',
    -- Step 4: Business
    target_audience  TEXT,
    competitors      TEXT,
    goals            TEXT,
    usp              TEXT COMMENT 'Unique selling proposition',
    -- Step 5: Colors
    primary_color    VARCHAR(7)   DEFAULT '#000000',
    secondary_color  VARCHAR(7)   DEFAULT '#ffffff',
    accent_color     VARCHAR(7)   DEFAULT '#5b5ef4',
    color_mood       VARCHAR(100) DEFAULT '',
    color_notes      TEXT,
    -- Step 6: Logo & Typography
    logo_url         VARCHAR(500) DEFAULT '',
    heading_font     VARCHAR(100) DEFAULT '',
    body_font        VARCHAR(100) DEFAULT '',
    brand_style      VARCHAR(100) DEFAULT '',
    brand_personality TEXT,
    -- Step 7: Generated spec
    ai_spec          LONGTEXT COMMENT 'AI-generated full spec markdown',
    generated_at     TIMESTAMP NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Client Portal Activity Log ────────────────────────────────────────────────
CREATE TABLE portal_activity (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    client_id  INT NOT NULL,
    project_id INT NOT NULL,
    action     VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id)  REFERENCES clients(id)  ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
