-- =============================================
-- PART 1: CORE TABLES (No Dependencies)
-- =============================================

-- Users Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role VARCHAR(50),
    credits INTEGER DEFAULT 5000,
    api_key TEXT UNIQUE,
    profile_pic_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Audiences Table
CREATE TABLE audiences (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL CHECK (status IN ('Draft', 'Saved')),
    source VARCHAR(50),
    criteria JSONB,
    count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_modified TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    objective VARCHAR(255),
    sender_infra JSONB
);

-- SMTP Services Table
CREATE TABLE smtp_services (
    id SERIAL PRIMARY KEY,
    from_name VARCHAR(255),
    from_email VARCHAR(255),
    name VARCHAR(255) NOT NULL,
    service_type VARCHAR(50) NOT NULL CHECK (service_type IN ('gmail', 'platform', 'custom')),
    smtp_host VARCHAR(255) NOT NULL,
    smtp_port INTEGER NOT NULL,
    smtp_username VARCHAR(255) NOT NULL,
    smtp_password TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================
-- PART 2: DEPENDENT TABLES
-- =============================================

-- Tracking Domains Table
-- ** MOVED UP: This table must exist before 'campaigns' can reference it. **
CREATE TABLE tracking_domains (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    domain_name VARCHAR(255) NOT NULL,
    cname_target VARCHAR(255),
    status VARCHAR(50) NOT NULL DEFAULT 'Pending' CHECK (status IN ('Pending', 'Verified', 'Failed')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, domain_name)
);

-- Campaigns Table
-- This table can now be created because `users`, `audiences`, and `tracking_domains` exist.
CREATE TABLE campaigns (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL CHECK (status IN ('Draft', 'Scheduled', 'Sending', 'Sent', 'Failed')),
    objective VARCHAR(255),
    audience_list_id INTEGER REFERENCES audiences(id) ON DELETE SET NULL,
    tracking_domain_id INTEGER REFERENCES tracking_domains(id) ON DELETE SET NULL,
    sequence JSONB,
    warmup BOOLEAN DEFAULT FALSE,
    throttling BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_modified TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Prospects Table
CREATE TABLE prospects (
    id SERIAL PRIMARY KEY,
    audience_id INTEGER NOT NULL REFERENCES audiences(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    email TEXT NOT NULL,
    UNIQUE (audience_id, email)
);

-- Custom Senders Table
CREATE TABLE custom_senders (
    id SERIAL PRIMARY KEY,
    smtp_service_id INTEGER NOT NULL UNIQUE REFERENCES smtp_services(id) ON DELETE CASCADE,
    domain_name VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(50) NOT NULL DEFAULT 'Pending' CHECK (status IN ('Pending', 'Verified', 'Failed')),
    spf_host VARCHAR(255) NOT NULL DEFAULT '@',
    spf_value TEXT NOT NULL,
    dkim_host VARCHAR(255) NOT NULL DEFAULT 'dkim._domainkey',
    dkim_value TEXT NOT NULL,
    dmarc_host VARCHAR(255) NOT NULL DEFAULT '_dmarc',
    dmarc_value TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    verified_at TIMESTAMPTZ
);

-- Templates Table
CREATE TABLE templates (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    body TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMPTZ
);

-- =============================================
-- PART 3: JOIN TABLES & LOGS
-- =============================================

-- Campaign Senders (Join Table)
CREATE TABLE campaign_senders (
    campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    smtp_service_id INTEGER NOT NULL REFERENCES smtp_services(id) ON DELETE CASCADE,
    PRIMARY KEY (campaign_id, smtp_service_id)
);

-- Email Events Table (For Analytics)
CREATE TABLE email_events (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    prospect_email TEXT NOT NULL,
    smtp_service_id INTEGER REFERENCES smtp_services(id) ON DELETE SET NULL,
    event_type VARCHAR(50) NOT NULL CHECK (event_type IN ('SENT', 'OPEN', 'CLICK', 'REPLY', 'BOUNCE', 'UNSUBSCRIBE')),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB
);

-- Indexes for performance
CREATE INDEX idx_email_events_campaign_id ON email_events(campaign_id);
CREATE INDEX idx_email_events_event_type ON email_events(event_type);

-- Activity Logs Table
CREATE TABLE activity_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    action VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    details JSONB
);

-- Sequence Steps Table
CREATE TABLE sequence_steps (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER REFERENCES campaigns(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    delay INTEGER,
    condition VARCHAR(50),
    template_id INTEGER REFERENCES templates(id)
);

-- Campaign Analytics Table (Legacy, consider replacing with email_events)
CREATE TABLE campaign_analytics (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER REFERENCES campaigns(id) ON DELETE CASCADE,
    sent INTEGER DEFAULT 0,
    open_rate FLOAT DEFAULT 0,
    click_rate FLOAT DEFAULT 0,
    bounce_rate FLOAT DEFAULT 0,
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- PART 4: COMMENTS (These now work correctly)
-- =============================================

COMMENT ON TABLE smtp_services IS 'Stores core SMTP credentials for all sending services.';
COMMENT ON TABLE custom_senders IS 'Stores domain, DNS, and verification status for custom senders.';
COMMENT ON COLUMN custom_senders.smtp_service_id IS 'Foreign key linking to the smtp_services table.';
COMMENT ON TABLE campaign_senders IS 'Join table to link multiple sending services to a single campaign for rotation.';
COMMENT ON TABLE prospects IS 'Stores individual contacts for an audience list.';
COMMENT ON TABLE tracking_domains IS 'Stores user-configured custom tracking domains.';

-- This comment now works because the 'campaigns' table is created after 'tracking_domains'.
COMMENT ON COLUMN campaigns.tracking_domain_id IS 'The specific tracking domain chosen for this campaign. If NULL, a default will be used.';

