-- AC Tool PostgreSQL schema
-- Run this once (idempotent — safe to re-run).

CREATE TABLE IF NOT EXISTS users (
    id          TEXT PRIMARY KEY,
    email       TEXT NOT NULL DEFAULT '',
    name        TEXT NOT NULL DEFAULT '',
    role        TEXT NOT NULL DEFAULT 'user',
    active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS clients (
    id                   TEXT PRIMARY KEY,
    name                 TEXT NOT NULL,
    has_custom_dropdowns BOOLEAN NOT NULL DEFAULT FALSE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- NULL client_id = global hierarchy; non-null = per-client override
CREATE TABLE IF NOT EXISTS dropdown_categories (
    id          SERIAL PRIMARY KEY,
    client_id   TEXT REFERENCES clients(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'Active',
    media_types JSONB NOT NULL DEFAULT '[]'
);
CREATE INDEX IF NOT EXISTS idx_dropdown_cat_client ON dropdown_categories(client_id);

-- scope: 'global' | 'client:<id>' | 'user:<id>'
CREATE TABLE IF NOT EXISTS export_templates (
    scope      TEXT PRIMARY KEY,
    columns    JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sheets (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL,
    name        TEXT NOT NULL,
    client_id   TEXT REFERENCES clients(id) ON DELETE SET NULL,
    data        JSONB NOT NULL DEFAULT '[]',
    item_count  INTEGER NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sheets_user ON sheets(user_id);