-- ═══════════════════════════════════════════════════════════════════════════
-- 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;

-- ── Users ─────────────────────────────────────────────────────────────────────
CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) UNIQUE NOT NULL,
    password   VARCHAR(255) NOT NULL,
    role       ENUM('admin','designer') DEFAULT 'designer',
    company    VARCHAR(150),
    logo       VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Clients ───────────────────────────────────────────────────────────────────
CREATE TABLE clients (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    user_id          INT NOT NULL,
    name             VARCHAR(150) NOT NULL,
    email            VARCHAR(150) NOT NULL,
    company          VARCHAR(150),
    phone            VARCHAR(50),
    address          TEXT,
    currency         ENUM('USD','EUR','GBP') DEFAULT 'USD',
    portal_token     VARCHAR(64) UNIQUE,
    portal_password  VARCHAR(255),
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 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;

-- ── Studio Settings ───────────────────────────────────────────────────────────
CREATE TABLE studio_settings (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    user_id              INT NOT NULL UNIQUE,
    -- Branding
    studio_name          VARCHAR(200) DEFAULT '',
    owner_name           VARCHAR(200) DEFAULT '',
    address              TEXT         DEFAULT '',
    tax_id               VARCHAR(100) DEFAULT '',
    phone                VARCHAR(50)  DEFAULT '',
    email                VARCHAR(150) DEFAULT '',
    website              VARCHAR(255) DEFAULT '',
    logo_url             VARCHAR(500) DEFAULT '',
    accent_color         VARCHAR(7)   DEFAULT '#5b5ef4',
    -- Bank / payment
    bank_details         TEXT         DEFAULT '',
    -- Invoice config
    invoice_prefix       VARCHAR(20)  DEFAULT 'INV',
    invoice_next_number  INT          DEFAULT 1,
    default_currency     ENUM('USD','EUR','GBP') DEFAULT 'USD',
    payment_due_days     INT          DEFAULT 30,
    invoice_footer       TEXT         DEFAULT 'Thank you for your business!',
    -- SMTP
    smtp_host            VARCHAR(255) DEFAULT '',
    smtp_port            INT          DEFAULT 587,
    smtp_user            VARCHAR(255) DEFAULT '',
    smtp_pass            VARCHAR(255) DEFAULT '',
    smtp_from_name       VARCHAR(200) DEFAULT '',
    smtp_encryption      ENUM('tls','ssl','none') DEFAULT 'tls',
    -- AI
    ai_provider          ENUM('deepseek','anthropic') DEFAULT 'deepseek',
    deepseek_api_key     VARCHAR(255) DEFAULT '',
    anthropic_api_key    VARCHAR(255) DEFAULT '',
    -- Timestamps
    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
) 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;

-- ── Demo admin user (password: admin123) ──────────────────────────────────────
-- Remove or change this before going live!
INSERT INTO users (name, email, password, role, company) VALUES
('Demo Designer', 'demo@debrieft.test', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Studio Debrieft');