-- NEW astpm — SQLite schema (v3 frozen baseline)
-- Run once via install.php. App opens with PRAGMA foreign_keys = ON (per-connection, set in PHP).

PRAGMA foreign_keys = ON;

-- ── Config ────────────────────────────────────────────────
CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);
INSERT INTO settings (key, value) VALUES ('designer_hourly_rate', '0');

-- ── Users & roles ─────────────────────────────────────────
CREATE TABLE users (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL CHECK (role IN ('admin','sales','production')),
    active        INTEGER NOT NULL DEFAULT 1,   -- 0 = frozen (never deleted)
    language      TEXT DEFAULT 'el',
    created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Companies (accounts) ──────────────────────────────────
CREATE TABLE companies (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    tax_id      TEXT,          -- ΑΦΜ
    tax_office  TEXT,          -- ΔΟΥ
    industry    TEXT,
    website     TEXT,
    country     TEXT,
    owner_id    INTEGER REFERENCES users(id),
    notes       TEXT,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Contacts (the person — stable across job changes) ─────
CREATE TABLE contacts (
    id          INTEGER PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    email       TEXT,
    phone       TEXT,
    source      TEXT,
    notes       TEXT,          -- legacy/pre-system company & role history (free text)
    owner_id    INTEGER REFERENCES users(id),
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Affiliations (person ↔ company role, time-bound) ──────
CREATE TABLE affiliations (
    id          INTEGER PRIMARY KEY,
    contact_id  INTEGER NOT NULL REFERENCES contacts(id),
    company_id  INTEGER NOT NULL REFERENCES companies(id),
    job_title   TEXT,
    is_primary  INTEGER NOT NULL DEFAULT 1,
    start_date  TEXT,
    end_date    TEXT,          -- NULL = current role
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Lists (admin-managed dropdowns) ───────────────────────
CREATE TABLE project_types (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE overhead_categories (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, active INTEGER NOT NULL DEFAULT 1
);

-- Seed: project types from Asterias activity sectors (5 of 8 known)
INSERT INTO project_types (name) VALUES
 ('Branding & Identity'),
 ('Packaging Design'),
 ('Digital & Web'),
 ('Video Production'),
 ('Consulting & AI');

INSERT INTO overhead_categories (name) VALUES
 ('Rent'),('Software / Licenses'),('Accounting'),('Utilities'),('Salaries'),('Misc');

-- ── Client products (Προϊόν Πελάτη) — per company ────────
CREATE TABLE client_products (
    id         INTEGER PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id),
    name       TEXT NOT NULL,
    active     INTEGER NOT NULL DEFAULT 1,
    UNIQUE (company_id, name)
);

-- ── Asterias service catalog (Προϊόν asteria) — grouped ──
CREATE TABLE asterias_service_categories (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
    sort_order INTEGER, active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE asterias_services (
    id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES asterias_service_categories(id),
    name TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1,
    UNIQUE (category_id, name)
);

-- ── Task characterization (Χαρακτηρισμός) ─────────────────
CREATE TABLE task_categories (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, active INTEGER NOT NULL DEFAULT 1
);

-- ── Deals / Offers (pipeline, pre-win) ────────────────────
CREATE TABLE deals (
    id                  INTEGER PRIMARY KEY,
    title               TEXT NOT NULL,
    company_id          INTEGER NOT NULL REFERENCES companies(id),
    contact_id          INTEGER REFERENCES contacts(id),
    value               REAL,
    currency            TEXT DEFAULT 'EUR',
    stage               TEXT NOT NULL DEFAULT 'lead'
                          CHECK (stage IN ('lead','qualified','proposal','won','lost')),
    probability         INTEGER,
    expected_close_date TEXT,
    owner_id            INTEGER REFERENCES users(id),
    po_number           TEXT,
    offer_type          TEXT CHECK (offer_type IN ('word_onedrive','billit')),
    offer_url           TEXT,
    won_at              TEXT,
    created_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Projects (PN######, minted on Won) ────────────────────
CREATE TABLE projects (
    id              INTEGER PRIMARY KEY,
    code            TEXT UNIQUE,              -- 'PN######', set by trigger
    company_id      INTEGER NOT NULL REFERENCES companies(id),
    deal_id         INTEGER REFERENCES deals(id),
    title           TEXT NOT NULL,
    project_type_id INTEGER REFERENCES project_types(id),
    status          TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed')),
    responsible_id  INTEGER REFERENCES users(id),
    po_number       TEXT,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    closed_at       TEXT
);

CREATE TRIGGER trg_projects_code AFTER INSERT ON projects
BEGIN
    UPDATE projects SET code = 'PN' || printf('%06d', NEW.id) WHERE id = NEW.id;
END;

-- ── Invoices ──────────────────────────────────────────────
CREATE TABLE invoices (
    id          INTEGER PRIMARY KEY,
    project_id  INTEGER NOT NULL REFERENCES projects(id),
    billit_url  TEXT,
    invoice_date TEXT,
    amount      REAL,
    notes       TEXT,
    created_by  INTEGER REFERENCES users(id),
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Tasks (JN######) ─────────────────────────────────────
CREATE TABLE tasks (
    id                  INTEGER PRIMARY KEY,
    code                TEXT UNIQUE,
    project_id          INTEGER NOT NULL REFERENCES projects(id),
    asterias_service_id INTEGER REFERENCES asterias_services(id),
    client_product_id   INTEGER REFERENCES client_products(id),
    free_text           TEXT,
    title               TEXT,
    task_category_id    INTEGER REFERENCES task_categories(id),
    estimated_hours     REAL,
    responsible_id      INTEGER REFERENCES users(id),
    work_status         TEXT NOT NULL DEFAULT 'open'
                          CHECK (work_status IN ('open','in_progress','done')),
    billing_status      TEXT NOT NULL DEFAULT 'uninvoiced'
                          CHECK (billing_status IN ('uninvoiced','invoiced')),
    invoice_id          INTEGER REFERENCES invoices(id),
    comments            TEXT,
    due_date            TEXT,
    created_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TRIGGER trg_tasks_code AFTER INSERT ON tasks
BEGIN
    UPDATE tasks SET code = 'JN' || printf('%06d', NEW.id) WHERE id = NEW.id;
END;

-- ── Task assignments (many designers per task) ────────────
CREATE TABLE task_assignments (
    id       INTEGER PRIMARY KEY,
    task_id  INTEGER NOT NULL REFERENCES tasks(id),
    user_id  INTEGER NOT NULL REFERENCES users(id),
    UNIQUE (task_id, user_id)
);

-- ── Time entries (minutes, display HH:MM) ─────────────────
CREATE TABLE time_entries (
    id         INTEGER PRIMARY KEY,
    task_id    INTEGER NOT NULL REFERENCES tasks(id),
    user_id    INTEGER NOT NULL REFERENCES users(id),
    entry_date TEXT NOT NULL,
    minutes    INTEGER NOT NULL,
    note       TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Staging — Gemini-scanned supplier invoices ────────────
CREATE TABLE staging_expenses (
    id             INTEGER PRIMARY KEY,
    import_batch   TEXT,
    supplier       TEXT,
    invoice_date   TEXT,
    amount         REAL,
    description    TEXT,
    drive_url      TEXT,
    route          TEXT CHECK (route IN ('task','overhead','skip')),
    status         TEXT NOT NULL DEFAULT 'unallocated'
                     CHECK (status IN ('unallocated','allocated','ignored')),
    allocated_task_id INTEGER REFERENCES tasks(id),
    allocated_by   INTEGER REFERENCES users(id),
    allocated_at   TEXT,
    created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Expenses ──────────────────────────────────────────────
CREATE TABLE expenses (
    id                   INTEGER PRIMARY KEY,
    expense_type         TEXT NOT NULL CHECK (expense_type IN ('project','overhead')),
    project_id           INTEGER REFERENCES projects(id),
    task_id              INTEGER REFERENCES tasks(id),
    overhead_category_id INTEGER REFERENCES overhead_categories(id),
    service_kind         TEXT,
    vendor               TEXT,
    qty                  REAL,
    supplier_quote       REAL,
    supplier_cost        REAL,
    client_charge        REAL,
    description          TEXT,
    source_url           TEXT,
    staging_id           INTEGER REFERENCES staging_expenses(id),
    expense_date         TEXT,
    created_by           INTEGER REFERENCES users(id),
    created_at           TEXT NOT NULL DEFAULT (datetime('now')),
    CHECK (
        (expense_type='overhead' AND overhead_category_id IS NOT NULL
            AND project_id IS NULL AND task_id IS NULL)
     OR (expense_type='project' AND overhead_category_id IS NULL
            AND (project_id IS NOT NULL OR task_id IS NOT NULL))
    )
);

-- ── Activity log ──────────────────────────────────────────
CREATE TABLE activity_log (
    id          INTEGER PRIMARY KEY,
    entity_type TEXT NOT NULL,
    entity_id   INTEGER,
    action      TEXT NOT NULL,
    detail      TEXT,
    user_id     INTEGER REFERENCES users(id),
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ── Indexes on hot FKs ────────────────────────────────────
CREATE INDEX idx_affiliations_contact ON affiliations(contact_id);
CREATE INDEX idx_affiliations_company ON affiliations(company_id);
CREATE INDEX idx_contacts_owner       ON contacts(owner_id);
CREATE INDEX idx_deals_company        ON deals(company_id);
CREATE INDEX idx_deals_stage          ON deals(stage);
CREATE INDEX idx_projects_company     ON projects(company_id);
CREATE INDEX idx_projects_status      ON projects(status);
CREATE INDEX idx_tasks_project        ON tasks(project_id);
CREATE INDEX idx_tasks_workstatus     ON tasks(work_status);
CREATE INDEX idx_tasks_billing        ON tasks(billing_status);
CREATE INDEX idx_tasks_invoice        ON tasks(invoice_id);
CREATE INDEX idx_taskassign_task      ON task_assignments(task_id);
CREATE INDEX idx_taskassign_user      ON task_assignments(user_id);
CREATE INDEX idx_time_task            ON time_entries(task_id);
CREATE INDEX idx_time_user            ON time_entries(user_id);
CREATE INDEX idx_expenses_project     ON expenses(project_id);
CREATE INDEX idx_expenses_type        ON expenses(expense_type);
CREATE INDEX idx_invoices_project     ON invoices(project_id);
CREATE INDEX idx_log_entity           ON activity_log(entity_type, entity_id);