-- MODULE: Warehouse | FILE: migrations/001_initial_schema.sql
-- Initial canonical schema — all tables use UUID PKs and carry timestamps.
-- Run against a PostgreSQL 16+ database with the uuid-ossp extension.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ─── Organisation & Workspace ─────────────────────────────────────────

CREATE TABLE organisations (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(255)  NOT NULL,
    plan        VARCHAR(50)   NOT NULL DEFAULT 'free',
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE workspaces (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id          UUID          NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
    ad_account_ids  TEXT[]        DEFAULT '{}',
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── Users ────────────────────────────────────────────────────────────

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id    UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    email           VARCHAR(320)  NOT NULL UNIQUE,
    role            VARCHAR(50)   NOT NULL DEFAULT 'viewer',
    hashed_password TEXT          NOT NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users(email);

-- ─── Campaigns / Ad Sets / Ads ────────────────────────────────────────

CREATE TABLE campaigns (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id  UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    platform      VARCHAR(50)   NOT NULL,
    external_id   VARCHAR(255),
    name          VARCHAR(500)  NOT NULL,
    status        VARCHAR(50)   NOT NULL DEFAULT 'draft',
    objective     VARCHAR(100),
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX idx_campaigns_external_id ON campaigns(external_id);

CREATE TABLE ad_sets (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    campaign_id     UUID          NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    name            VARCHAR(500)  NOT NULL,
    targeting_json  JSONB         DEFAULT '{}',
    budget          DOUBLE PRECISION DEFAULT 0.0,
    bid_strategy    VARCHAR(100),
    status          VARCHAR(50)   NOT NULL DEFAULT 'draft',
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE creatives (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id  UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    type          VARCHAR(50)   NOT NULL,
    thumbnail_url TEXT,
    headline      VARCHAR(500),
    body_text     TEXT,
    cta           VARCHAR(100),
    platform      VARCHAR(50),
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE ads (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ad_set_id   UUID          NOT NULL REFERENCES ad_sets(id) ON DELETE CASCADE,
    creative_id UUID          REFERENCES creatives(id) ON DELETE SET NULL,
    name        VARCHAR(500)  NOT NULL,
    status      VARCHAR(50)   NOT NULL DEFAULT 'draft',
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── Audiences ────────────────────────────────────────────────────────

CREATE TABLE audiences (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id    UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    platform        VARCHAR(50)   NOT NULL,
    external_id     VARCHAR(255),
    name            VARCHAR(500)  NOT NULL,
    size            INTEGER       DEFAULT 0,
    targeting_json  JSONB         DEFAULT '{}',
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX idx_audiences_external_id ON audiences(external_id);

-- ─── Metrics ──────────────────────────────────────────────────────────

CREATE TABLE daily_metrics (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50)      NOT NULL,
    entity_id   UUID             NOT NULL,
    date        DATE             NOT NULL,
    spend       DOUBLE PRECISION DEFAULT 0.0,
    impressions INTEGER          DEFAULT 0,
    clicks      INTEGER          DEFAULT 0,
    leads       INTEGER          DEFAULT 0,
    cpa         DOUBLE PRECISION DEFAULT 0.0,
    ctr         DOUBLE PRECISION DEFAULT 0.0,
    roas        DOUBLE PRECISION DEFAULT 0.0,
    platform    VARCHAR(50)      NOT NULL,
    created_at  TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE INDEX idx_daily_metrics_entity  ON daily_metrics(entity_type, entity_id);
CREATE INDEX idx_daily_metrics_date    ON daily_metrics(date);

CREATE TABLE hourly_metrics (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_id   UUID             NOT NULL,
    hour        TIMESTAMPTZ      NOT NULL,
    spend       DOUBLE PRECISION DEFAULT 0.0,
    impressions INTEGER          DEFAULT 0,
    leads       INTEGER          DEFAULT 0,
    created_at  TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE INDEX idx_hourly_metrics_entity ON hourly_metrics(entity_id);
CREATE INDEX idx_hourly_metrics_hour   ON hourly_metrics(hour);

-- ─── Leads & CRM ─────────────────────────────────────────────────────

CREATE TABLE leads (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id    UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    campaign_id     UUID          REFERENCES campaigns(id) ON DELETE SET NULL,
    ad_id           UUID          REFERENCES ads(id) ON DELETE SET NULL,
    form_data_json  JSONB         DEFAULT '{}',
    quality_score   DOUBLE PRECISION,
    crm_id          VARCHAR(255),
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE crm_events (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lead_id    UUID             NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
    status     VARCHAR(100)     NOT NULL,
    revenue    DOUBLE PRECISION DEFAULT 0.0,
    created_at TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE TABLE offline_sync_logs (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lead_id       UUID          NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
    platform      VARCHAR(50)   NOT NULL,
    sent_at       TIMESTAMPTZ,
    response_code INTEGER,
    error         TEXT,
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── Alerts & Recommendations ─────────────────────────────────────────

CREATE TABLE alerts (
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    severity     VARCHAR(50)   NOT NULL DEFAULT 'info',
    entity_id    UUID,
    alert_type   VARCHAR(100)  NOT NULL,
    message      TEXT          NOT NULL,
    is_resolved  BOOLEAN       DEFAULT FALSE,
    created_at   TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE recommendations (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id    UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    type            VARCHAR(100)  NOT NULL,
    entity_id       UUID,
    action          TEXT          NOT NULL,
    expected_impact TEXT,
    status          VARCHAR(50)   NOT NULL DEFAULT 'pending',
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── ML & Forecasting ────────────────────────────────────────────────

CREATE TABLE ml_predictions (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    model_name      VARCHAR(255)     NOT NULL,
    entity_id       UUID             NOT NULL,
    prediction_date DATE             NOT NULL,
    value           DOUBLE PRECISION NOT NULL,
    confidence      DOUBLE PRECISION,
    created_at      TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE INDEX idx_ml_predictions_model  ON ml_predictions(model_name);
CREATE INDEX idx_ml_predictions_entity ON ml_predictions(entity_id);

CREATE TABLE forecasts (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_id     UUID             NOT NULL,
    metric        VARCHAR(100)     NOT NULL,
    forecast_date DATE             NOT NULL,
    value_low     DOUBLE PRECISION NOT NULL,
    value_mid     DOUBLE PRECISION NOT NULL,
    value_high    DOUBLE PRECISION NOT NULL,
    created_at    TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE INDEX idx_forecasts_entity ON forecasts(entity_id);

-- ─── A/B Testing ─────────────────────────────────────────────────────

CREATE TABLE ab_tests (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id  UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name          VARCHAR(500)  NOT NULL,
    hypothesis    TEXT,
    variants_json JSONB         DEFAULT '[]',
    status        VARCHAR(50)   NOT NULL DEFAULT 'draft',
    winner_id     UUID,
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── Attribution ──────────────────────────────────────────────────────

CREATE TABLE attribution_credits (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversion_id   UUID             NOT NULL,
    channel         VARCHAR(100)     NOT NULL,
    model           VARCHAR(100)     NOT NULL,
    credit_fraction DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    revenue         DOUBLE PRECISION DEFAULT 0.0,
    created_at      TIMESTAMPTZ      NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE INDEX idx_attribution_conversion ON attribution_credits(conversion_id);

-- ─── Competitive Intelligence ─────────────────────────────────────────

CREATE TABLE competitor_ads (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id    UUID          NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    page_id         VARCHAR(255)  NOT NULL,
    ad_snapshot_url TEXT,
    start_date      DATE,
    themes_json     JSONB         DEFAULT '[]',
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- ─── Notifications ────────────────────────────────────────────────────

CREATE TABLE notification_logs (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID          NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    channel    VARCHAR(50)   NOT NULL,
    alert_id   UUID          REFERENCES alerts(id) ON DELETE SET NULL,
    sent_at    TIMESTAMPTZ   DEFAULT now(),
    status     VARCHAR(50)   NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ   NOT NULL DEFAULT now()
);
