-- Campaigns table to track email campaigns
create table campaigns (
    id serial primary key,
    name varchar(255) not null,
    start_date date,
    end_date date,
    status varchar(50) default 'pending'
);

-- Email templates table to store fixed email content
create table email_templates (
    id serial primary key,
    subject varchar(255),
    body_html text
);

-- Leads table to store scraped lead data
create table leads (
    id serial primary key,
    search_query varchar(255),
    name varchar(255),
    email varchar(255),
    url varchar(1000),
    foreign key (search_query) references search_terms(term)
);

-- Generated_emails table to store AI-generated or fixed emails
create table generated_emails (
    id serial primary key,
    lead_id integer,
    search_term varchar(255),
    email varchar(255),
    url varchar(1000),
    generated_email text,
    subject varchar(255),
    sent_status boolean default false,
    sent_at timestamp,
    email_sent boolean default false,
    foreign key (lead_id) references leads(id)
);

-- Search_terms table to store and track search queries
create table search_terms (
    id serial primary key,
    term varchar(255),
    status varchar(50) default 'pending',
    fetched_emails integer default 0
);