-- Artist promotional campaigns (separate from system promotions)
CREATE TABLE IF NOT EXISTS artist_campaigns (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    artist_id UUID NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
    track_id UUID REFERENCES tracks(id) ON DELETE SET NULL,
    title VARCHAR(255) NOT NULL,
    objective VARCHAR(50) DEFAULT 'streams', -- streams, followers, awareness
    budget INTEGER DEFAULT 0, -- In FCFA
    spent INTEGER DEFAULT 0,
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'active', 'paused', 'completed')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Scheduled releases
CREATE TABLE IF NOT EXISTS scheduled_releases (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    artist_id UUID NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    release_type VARCHAR(50) NOT NULL CHECK (release_type IN ('single', 'ep', 'album')),
    cover_url TEXT,
    release_date DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'scheduled' CHECK (status IN ('draft', 'scheduled', 'released', 'cancelled')),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_artist_campaigns_artist ON artist_campaigns(artist_id);
CREATE INDEX IF NOT EXISTS idx_artist_campaigns_status ON artist_campaigns(status);
CREATE INDEX IF NOT EXISTS idx_scheduled_releases_artist ON scheduled_releases(artist_id);
CREATE INDEX IF NOT EXISTS idx_scheduled_releases_date ON scheduled_releases(release_date);