-- Создание enum для состояний контента
CREATE TYPE content_state AS ENUM ('draft', 'validated', 'published');

-- Таблица кампаний
CREATE TABLE campaigns (
    id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    budget DECIMAL(10,2) NOT NULL,
    fluxion_target DECIMAL(3,2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Таблица контента
CREATE TABLE contents (
    id UUID PRIMARY KEY,
    campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    state content_state NOT NULL,
    engagement_potential DECIMAL(3,2),
    url TEXT,
    leads_generated INTEGER,
    roi DECIMAL(5,2),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    validated_at TIMESTAMP WITH TIME ZONE,
    published_at TIMESTAMP WITH TIME ZONE,
    
    -- Индексы для быстрого поиска
    CONSTRAINT fk_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id)
);

CREATE INDEX idx_contents_campaign_id ON contents(campaign_id);
CREATE INDEX idx_contents_state ON contents(state);
CREATE INDEX idx_campaigns_created_at ON campaigns(created_at);

-- Комментарии для документации
COMMENT ON TABLE campaigns IS 'Маркетинговые кампании';
COMMENT ON TABLE contents IS 'Единицы контента в рамках кампаний';
COMMENT ON COLUMN contents.leads_generated IS 'Количество привлеченных лидов';
COMMENT ON COLUMN contents.roi IS 'Return on Investment в процентах';
