CREATE TABLE IF NOT EXISTS franchise_offers (
    id                     SERIAL PRIMARY KEY,
    franchise_id           INTEGER NOT NULL,
    title                  VARCHAR(255) NOT NULL,
    description            TEXT NOT NULL DEFAULT '',
    objective              VARCHAR(50) NOT NULL DEFAULT 'generate_client',
    commission_per_dossier NUMERIC(10,2) NOT NULL CHECK (commission_per_dossier >= 100),
    reward                 TEXT NOT NULL DEFAULT '',
    start_date             DATE NOT NULL,
    end_date               DATE NOT NULL,
    status                 VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_at             TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_franchise_offers_franchise_id ON franchise_offers(franchise_id);
CREATE INDEX IF NOT EXISTS idx_franchise_offers_status ON franchise_offers(status);
