ALTER TABLE commissions ADD COLUMN IF NOT EXISTS payment_due_date TIMESTAMP NULL;
ALTER TABLE commissions ADD COLUMN IF NOT EXISTS ambassador_confirmed_received BOOLEAN NULL;
ALTER TABLE commissions ADD COLUMN IF NOT EXISTS ambassador_confirmed_at TIMESTAMP NULL;
ALTER TABLE commissions ADD COLUMN IF NOT EXISTS confirmation_notified_at TIMESTAMP NULL;

CREATE TABLE IF NOT EXISTS notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    payload JSONB NULL,
    read_at TIMESTAMP NULL,
    action_required BOOLEAN NOT NULL DEFAULT FALSE,
    action_done_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_unread ON notifications(user_id, read_at) WHERE read_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_notifications_action_pending ON notifications(user_id, action_done_at) WHERE action_required = TRUE AND action_done_at IS NULL;

CREATE TABLE IF NOT EXISTS commission_payment_alerts (
    id SERIAL PRIMARY KEY,
    commission_id INTEGER NOT NULL REFERENCES commissions(id) ON DELETE CASCADE,
    dossier_id INTEGER NOT NULL REFERENCES dossiers(id),
    ambassador_user_id INTEGER NOT NULL REFERENCES users(id),
    franchisee_user_id INTEGER NULL REFERENCES users(id),
    status VARCHAR(50) NOT NULL DEFAULT 'open',
    ambassador_response TEXT NULL,
    admin_notes TEXT NULL,
    proof_requested_at TIMESTAMP NULL,
    regularized_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_alert_per_commission UNIQUE (commission_id)
);

CREATE INDEX IF NOT EXISTS idx_cpa_status ON commission_payment_alerts(status);
CREATE INDEX IF NOT EXISTS idx_cpa_franchisee ON commission_payment_alerts(franchisee_user_id);
CREATE INDEX IF NOT EXISTS idx_cpa_ambassador ON commission_payment_alerts(ambassador_user_id)
