schema.sql

-- =========================
-- EXTENSIONS (Supabase/PostgreSQL)
-- =========================
-- Supabase supporte pgcrypto; gen_random_uuid() est recommandé
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- =========================
-- 1. ROLES
-- =========================
CREATE TABLE IF NOT EXISTS roles (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom VARCHAR(20) UNIQUE
);

INSERT INTO roles (nom) VALUES ('client'), ('vendeur'), ('admin'), ('livreur')
ON CONFLICT (nom) DO NOTHING;

-- =========================
-- 2. UTILISATEURS
-- =========================
CREATE TABLE IF NOT EXISTS utilisateurs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nom VARCHAR(100),
    telephone VARCHAR(20) UNIQUE NOT NULL,
    mot_de_passe TEXT NOT NULL,
    image_url TEXT,
    role_id INT REFERENCES roles(id),
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 3. OTP (authentification)
-- =========================
CREATE TABLE IF NOT EXISTS otp_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    telephone VARCHAR(20),
    code VARCHAR(10),
    expire_le TIMESTAMPTZ
);

-- =========================
-- 4. ADRESSES
-- =========================
CREATE TABLE IF NOT EXISTS adresses_utilisateur (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    libelle VARCHAR(50),
    adresse TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8)
);

-- =========================
-- 5. ENTREPRISES (livraison)
-- =========================
CREATE TABLE IF NOT EXISTS entreprises_livraison (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nom VARCHAR(150),
    telephone VARCHAR(20)
);

-- =========================
-- 6. LIVREURS
-- =========================
CREATE TABLE IF NOT EXISTS livreurs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entreprise_id UUID REFERENCES entreprises_livraison(id) ON DELETE SET NULL,
    nom VARCHAR(100),
    telephone VARCHAR(20),
    type_vehicule VARCHAR(50),
    disponible BOOLEAN DEFAULT true
);

-- =========================
-- 7. POSITIONS LIVREURS
-- =========================
CREATE TABLE IF NOT EXISTS positions_livreurs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    livreur_id UUID REFERENCES livreurs(id) ON DELETE CASCADE,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    enregistre_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 8. ENTREPRISES (vendeurs)
-- =========================
CREATE TABLE IF NOT EXISTS entreprises (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proprietaire_id UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    nom VARCHAR(150),
    type VARCHAR(20), -- boutique / restaurant
    description TEXT,
    telephone VARCHAR(20),
    adresse TEXT,
    image_url TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    ouvert BOOLEAN DEFAULT true,
    statut_moderation VARCHAR(30) NOT NULL DEFAULT 'en_attente'
);

-- Si la table existait déjà sans image_url :
-- ALTER TABLE entreprises ADD COLUMN IF NOT EXISTS image_url TEXT;

-- =========================
-- 9. HORAIRES
-- =========================
CREATE TABLE IF NOT EXISTS horaires_entreprise (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE CASCADE,
    jour INT,
    ouverture TIME,
    fermeture TIME
);

-- =========================
-- 10. CATEGORIES
-- =========================
CREATE TABLE IF NOT EXISTS categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nom VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS entreprises_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE CASCADE,
    categorie_id UUID REFERENCES categories(id) ON DELETE CASCADE
);

-- =========================
-- 11. PRODUITS
-- =========================
CREATE TABLE IF NOT EXISTS produits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE CASCADE,
    nom VARCHAR(150),
    description TEXT,
    prix DECIMAL(12,2),
    stock INT,
    image_url TEXT
);

-- =========================
-- 12. MENUS (restaurant)
-- =========================
CREATE TABLE IF NOT EXISTS menus (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE CASCADE,
    nom VARCHAR(150),
    description TEXT,
    prix DECIMAL(12,2),
    temps_preparation INT,
    disponible BOOLEAN DEFAULT true
);

-- =========================
-- 13. PANIER
-- =========================
CREATE TABLE IF NOT EXISTS paniers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS panier_articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    panier_id UUID REFERENCES paniers(id) ON DELETE CASCADE,
    item_id UUID,
    type_item VARCHAR(20),
    quantite INT
);

-- =========================
-- 14. COMMANDES
-- =========================
CREATE TABLE IF NOT EXISTS commandes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE SET NULL,
    statut VARCHAR(30),
    prix_total DECIMAL(12,2),
    adresse_livraison TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS commande_articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commande_id UUID REFERENCES commandes(id) ON DELETE CASCADE,
    item_id UUID,
    type_item VARCHAR(20),
    quantite INT,
    prix DECIMAL(12,2)
);

-- =========================
-- 15. LIVRAISONS
-- =========================
CREATE TABLE IF NOT EXISTS livraisons (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commande_id UUID REFERENCES commandes(id) ON DELETE CASCADE,
    livreur_id UUID REFERENCES livreurs(id) ON DELETE SET NULL,
    statut VARCHAR(30),
    assigne_le TIMESTAMPTZ,
    livre_le TIMESTAMPTZ
);

-- =========================
-- 16. PAIEMENTS
-- =========================
CREATE TABLE IF NOT EXISTS paiements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commande_id UUID REFERENCES commandes(id) ON DELETE CASCADE,
    montant DECIMAL(12,2),
    methode VARCHAR(50),
    statut VARCHAR(20),
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 17. REMBOURSEMENTS
-- =========================
CREATE TABLE IF NOT EXISTS remboursements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    paiement_id UUID REFERENCES paiements(id) ON DELETE CASCADE,
    montant DECIMAL(12,2),
    raison TEXT,
    statut VARCHAR(20),
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 18. PORTEFEUILLE (wallet)
-- =========================
CREATE TABLE IF NOT EXISTS portefeuilles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    solde DECIMAL(12,2) DEFAULT 0
);

CREATE TABLE IF NOT EXISTS transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portefeuille_id UUID REFERENCES portefeuilles(id) ON DELETE CASCADE,
    montant DECIMAL(12,2),
    type VARCHAR(20), -- debit / credit
    description TEXT,
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 19. NOTIFICATIONS
-- =========================
CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    titre VARCHAR(150),
    message TEXT,
    lu BOOLEAN DEFAULT false,
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 20. FAVORIS
-- =========================
CREATE TABLE IF NOT EXISTS favoris (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE CASCADE
);

-- =========================
-- 21. AVIS / NOTES
-- =========================
CREATE TABLE IF NOT EXISTS avis (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    entreprise_id UUID REFERENCES entreprises(id) ON DELETE SET NULL,
    livreur_id UUID REFERENCES livreurs(id) ON DELETE SET NULL,
    note INT,
    commentaire TEXT
);

-- =========================
-- 22. PROMOTIONS
-- =========================
CREATE TABLE IF NOT EXISTS promotions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50),
    reduction DECIMAL(5,2),
    expire_le TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS commandes_promotions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commande_id UUID REFERENCES commandes(id) ON DELETE CASCADE,
    promotion_id UUID REFERENCES promotions(id) ON DELETE CASCADE
);

-- =========================
-- 23. LOGS ADMIN
-- =========================
CREATE TABLE IF NOT EXISTS logs_admin (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_id UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    action TEXT,
    cree_le TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 24. SESSIONS
-- =========================
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    token TEXT,
    expire_le TIMESTAMPTZ
);

-- =========================
-- 25. PARAMETRES APP
-- =========================
CREATE TABLE IF NOT EXISTS parametres (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cle VARCHAR(100),
    valeur TEXT
);

-- =========================
-- PATCH DE MISE A NIVEAU (base existante)
-- =========================
-- Ce bloc permet d'aligner une base deja en production avec le schema attendu
-- sans supprimer de donnees.

ALTER TABLE IF EXISTS otp_codes
  ADD COLUMN IF NOT EXISTS id UUID DEFAULT gen_random_uuid();
ALTER TABLE IF EXISTS otp_codes
  ADD COLUMN IF NOT EXISTS telephone VARCHAR(20);
ALTER TABLE IF EXISTS otp_codes
  ADD COLUMN IF NOT EXISTS code VARCHAR(10);
ALTER TABLE IF EXISTS otp_codes
  ADD COLUMN IF NOT EXISTS expire_le TIMESTAMPTZ;

ALTER TABLE IF EXISTS entreprises
  ADD COLUMN IF NOT EXISTS image_url TEXT;

ALTER TABLE IF EXISTS entreprises
  ADD COLUMN IF NOT EXISTS statut_moderation VARCHAR(30);

UPDATE entreprises
SET statut_moderation = 'active'
WHERE statut_moderation IS NULL;

ALTER TABLE IF EXISTS entreprises
  ALTER COLUMN statut_moderation SET DEFAULT 'en_attente';

ALTER TABLE IF EXISTS entreprises
  ALTER COLUMN statut_moderation SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_roles_nom_unique ON roles (nom);
CREATE INDEX IF NOT EXISTS idx_otp_codes_telephone_code ON otp_codes (telephone, code);
CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions (token);
CREATE INDEX IF NOT EXISTS idx_commandes_utilisateur_id ON commandes (utilisateur_id);
CREATE INDEX IF NOT EXISTS idx_commandes_entreprise_id ON commandes (entreprise_id);
CREATE INDEX IF NOT EXISTS idx_livraisons_commande_id ON livraisons (commande_id);