-- Enums
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'userrole') THEN
        CREATE TYPE userrole AS ENUM ('ADMIN', 'USER');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'campaignstatus') THEN
        CREATE TYPE campaignstatus AS ENUM ('DRAFT', 'SCHEDULED', 'SENDING', 'DONE', 'FAILED');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'recipientstatus') THEN
        CREATE TYPE recipientstatus AS ENUM ('PENDING', 'SENT', 'DELIVERED', 'READ', 'FAILED');
    END IF;
END$$;

-- users
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR NOT NULL,
    password_hash VARCHAR NOT NULL,
    role userrole NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS ix_users_email ON users (email);
CREATE INDEX IF NOT EXISTS ix_users_id ON users (id);

-- contacts
CREATE TABLE IF NOT EXISTS contacts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    phone_e164 VARCHAR NOT NULL,
    first_name VARCHAR,
    last_name VARCHAR,
    email VARCHAR,
    opted_in BOOLEAN NOT NULL,
    conversation_window_open BOOLEAN NOT NULL,
    source VARCHAR,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    CONSTRAINT uq_user_phone UNIQUE (user_id, phone_e164)
);
CREATE INDEX IF NOT EXISTS ix_contacts_user_phone ON contacts (user_id, phone_e164);
CREATE INDEX IF NOT EXISTS ix_contacts_id ON contacts (id);
CREATE INDEX IF NOT EXISTS ix_contacts_user_id ON contacts (user_id);

-- contact_tags
CREATE TABLE IF NOT EXISTS contact_tags (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_contact_tags_id ON contact_tags (id);
CREATE INDEX IF NOT EXISTS ix_contact_tags_user_id ON contact_tags (user_id);

-- contact_tag_map
CREATE TABLE IF NOT EXISTS contact_tag_map (
    id SERIAL PRIMARY KEY,
    contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES contact_tags(id) ON DELETE CASCADE,
    CONSTRAINT uq_contact_tag UNIQUE (contact_id, tag_id)
);
CREATE INDEX IF NOT EXISTS ix_contact_tag_map_id ON contact_tag_map (id);

-- dnd_list
CREATE TABLE IF NOT EXISTS dnd_list (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    phone_e164 VARCHAR NOT NULL,
    reason TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    CONSTRAINT uq_dnd_user_phone UNIQUE (user_id, phone_e164)
);
CREATE INDEX IF NOT EXISTS ix_dnd_list_id ON dnd_list (id);
CREATE INDEX IF NOT EXISTS ix_dnd_list_phone_e164 ON dnd_list (phone_e164);
CREATE INDEX IF NOT EXISTS ix_dnd_list_user_id ON dnd_list (user_id);

-- lists
CREATE TABLE IF NOT EXISTS lists (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_lists_id ON lists (id);
CREATE INDEX IF NOT EXISTS ix_lists_user_id ON lists (user_id);

-- list_members
CREATE TABLE IF NOT EXISTS list_members (
    id SERIAL PRIMARY KEY,
    list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
    contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT uq_list_contact UNIQUE (list_id, contact_id)
);
CREATE INDEX IF NOT EXISTS ix_list_members_contact_id ON list_members (contact_id);
CREATE INDEX IF NOT EXISTS ix_list_members_id ON list_members (id);
CREATE INDEX IF NOT EXISTS ix_list_members_list_id ON list_members (list_id);

-- templates
CREATE TABLE IF NOT EXISTS templates (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    language VARCHAR NOT NULL,
    body_text TEXT NOT NULL,
    is_whatsapp_template BOOLEAN NOT NULL,
    provider_template_name VARCHAR,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_templates_id ON templates (id);
CREATE INDEX IF NOT EXISTS ix_templates_user_id ON templates (user_id);

-- campaigns
CREATE TABLE IF NOT EXISTS campaigns (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    template_id INTEGER REFERENCES templates(id) ON DELETE SET NULL,
    list_id INTEGER REFERENCES lists(id) ON DELETE SET NULL,
    status campaignstatus NOT NULL,
    scheduled_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_campaigns_id ON campaigns (id);
CREATE INDEX IF NOT EXISTS ix_campaigns_status ON campaigns (status);
CREATE INDEX IF NOT EXISTS ix_campaigns_user_id ON campaigns (user_id);

-- campaign_recipients
CREATE TABLE IF NOT EXISTS campaign_recipients (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
    status recipientstatus NOT NULL,
    provider_message_id VARCHAR,
    last_error TEXT,
    updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_campaign_status ON campaign_recipients (campaign_id, status);
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_campaign_id ON campaign_recipients (campaign_id);
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_contact_id ON campaign_recipients (contact_id);
CREATE INDEX IF NOT EXISTS ix_campaign_recipients_id ON campaign_recipients (id);

-- send_logs
CREATE TABLE IF NOT EXISTS send_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    campaign_id INTEGER REFERENCES campaigns(id) ON DELETE CASCADE,
    contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,
    provider VARCHAR NOT NULL,
    request_payload_json JSON,
    response_payload_json JSON,
    status VARCHAR NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_send_logs_campaign_id ON send_logs (campaign_id);
CREATE INDEX IF NOT EXISTS ix_send_logs_created_at ON send_logs (created_at);
CREATE INDEX IF NOT EXISTS ix_send_logs_id ON send_logs (id);
CREATE INDEX IF NOT EXISTS ix_send_logs_user_id ON send_logs (user_id);

-- jobs
CREATE TABLE IF NOT EXISTS jobs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR NOT NULL,
    payload_json JSON NOT NULL,
    status VARCHAR NOT NULL,
    attempts INTEGER NOT NULL,
    run_after TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_error TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_jobs_status_run_after ON jobs (status, run_after);
CREATE INDEX IF NOT EXISTS ix_jobs_id ON jobs (id);
CREATE INDEX IF NOT EXISTS ix_jobs_user_id ON jobs (user_id);

-- google_tokens
CREATE TABLE IF NOT EXISTS google_tokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    encrypted_token TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS ix_google_tokens_user_id ON google_tokens (user_id);
CREATE INDEX IF NOT EXISTS ix_google_tokens_id ON google_tokens (id);