-- 011_commission_alerts.sql
-- Système d'alerte paiement commission ambassadeur
-- Idempotente (ADD COLUMN IF NOT EXISTS, CREATE TABLE IF NOT EXISTS)

-- 1. Colonnes de suivi paiement sur commissions
ALTER TABLE commissions
    ADD COLUMN IF NOT EXISTS payment_due_date TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS ambassador_confirmed_received BOOLEAN NULL,
    ADD COLUMN IF NOT EXISTS ambassador_confirmed_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS confirmation_notified_at TIMESTAMP NULL;

COMMENT ON COLUMN commissions.payment_due_date IS 'Date limite de paiement (30j après statut signed du dossier)';
COMMENT ON COLUMN commissions.ambassador_confirmed_received IS 'TRUE = reçu, FALSE = non reçu, NULL = pas encore répondu';
COMMENT ON COLUMN commissions.ambassador_confirmed_at IS 'Date de la réponse de l''ambassadeur';
COMMENT ON COLUMN commissions.confirmation_notified_at IS 'Date d''envoi de la notification à l''ambassadeur (pour éviter les doublons)';

-- 2. Table notifications in-app
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;

-- 3. Table alertes admin commission
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',
    -- open | proof_requested | regularized | closed
    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);

-- Vérification
SELECT
    column_name,
    data_type
FROM information_schema.columns
WHERE table_name = 'commissions'
  AND column_name IN ('payment_due_date', 'ambassador_confirmed_received', 'ambassador_confirmed_at', 'confirmation_notified_at')
ORDER BY column_name;

SELECT table_name
FROM information_schema.tables
WHERE table_name IN ('notifications', 'commission_payment_alerts')
ORDER BY table_name;
