-- Babioto Database Schema
CREATE DATABASE IF NOT EXISTS atooumcbabioto DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE atooumcbabioto;

CREATE TABLE IF NOT EXISTS babi_users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(191) NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,
  role        ENUM('client','seller') NOT NULL DEFAULT 'client',
  first_name  VARCHAR(100),
  last_name   VARCHAR(100),
  company_name VARCHAR(200),
  siret       VARCHAR(50),
  phone       VARCHAR(30),
  address     VARCHAR(255),
  city        VARCHAR(100),
  zip_code    VARCHAR(20),
  avatar          VARCHAR(255),
  social_provider VARCHAR(20)  NULL,
  social_uid      VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS babi_categories (
  id         VARCHAR(50) PRIMARY KEY,
  label      VARCHAR(100) NOT NULL,
  icon       VARCHAR(100) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS babi_products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  brand       VARCHAR(100) NOT NULL,
  ref         VARCHAR(100) NOT NULL,
  kind        VARCHAR(50) NOT NULL,
  cat         VARCHAR(50) NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  old         DECIMAL(10,2),
  rating      DECIMAL(3,2) NOT NULL DEFAULT 0,
  reviews     INT NOT NULL DEFAULT 0,
  seller_id   INT,
  seller      VARCHAR(200),
  delivery    VARCHAR(50),
  best        TINYINT(1) NOT NULL DEFAULT 0,
  description TEXT,
  stock       INT DEFAULT 0,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cat) REFERENCES babi_categories(id) ON DELETE RESTRICT,
  FOREIGN KEY (seller_id) REFERENCES babi_users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS babi_orders (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  num             VARCHAR(30) NOT NULL UNIQUE,
  user_id         INT NOT NULL,
  address         TEXT,
  delivery_method VARCHAR(100),
  status          ENUM('new','pending','shipped','delivered','cancelled') NOT NULL DEFAULT 'new',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES babi_users(id)
);

CREATE TABLE IF NOT EXISTS babi_order_items (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  order_id    INT NOT NULL,
  product_id  INT NOT NULL,
  qty         INT NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES babi_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES babi_products(id)
);

CREATE TABLE IF NOT EXISTS babi_password_resets (
  email      VARCHAR(191) NOT NULL,
  token      VARCHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  PRIMARY KEY (email)
);

-- ─── Véhicules ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS babi_marques (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  nom  VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS babi_modeles (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  marque_id   INT NOT NULL,
  nom         VARCHAR(100) NOT NULL,
  annee_debut SMALLINT NOT NULL,
  annee_fin   SMALLINT NOT NULL,
  FOREIGN KEY (marque_id) REFERENCES babi_marques(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS babi_motorisations (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  modele_id   INT NOT NULL,
  annee_debut SMALLINT NOT NULL,
  annee_fin   SMALLINT NOT NULL,
  nom         VARCHAR(150) NOT NULL,
  carburant   ENUM('essence','diesel','hybride','electrique') NOT NULL DEFAULT 'essence',
  FOREIGN KEY (modele_id) REFERENCES babi_modeles(id) ON DELETE CASCADE
);

-- ─── Seed categories ───────────────────────────────────────────────────────
INSERT IGNORE INTO babi_categories (id, label, icon, sort_order) VALUES
  ('freinage',       'Freinage',        'disc-outline',              1),
  ('moteur',         'Moteur',          'cog-outline',               2),
  ('filtres',        'Filtres',         'funnel-outline',            3),
  ('allumage',       'Allumage',        'flash-outline',             4),
  ('suspension',     'Suspension',      'construct-outline',         5),
  ('batterie',       'Batterie',        'battery-charging-outline',  6),
  ('eclairage',      'Éclairage',       'bulb-outline',              7),
  ('echappement',    'Échappement',     'cloud-outline',             8),
  ('transmission',   'Transmission',    'git-network-outline',       9),
  ('climatisation',  'Climatisation',   'thermometer-outline',       10),
  ('direction',      'Direction',       'navigate-outline',          11),
  ('refroidissement','Refroidissement', 'water-outline',             12),
  ('carrosserie',    'Carrosserie',     'car-outline',               13),
  ('vitrage',        'Vitrage',         'scan-outline',              14),
  ('electronique',   'Électronique',    'hardware-chip-outline',     15),
  ('accessoires',    'Accessoires',     'grid-outline',              16),
  ('entretien',      'Entretien',       'flask-outline',             17),
  ('pneumatiques',   'Pneumatiques',    'ellipse-outline',           18);

-- ─── Seed demo seller ──────────────────────────────────────────────────────
INSERT IGNORE INTO babi_users (id, email, password, role, first_name, last_name, company_name, siret) VALUES
  (1, 'contact@autostock.fr', '$2y$10$demo_hash_placeholder', 'seller', 'AutoStock', 'Pro', 'AutoStock Pro', '123 456 789 00012');

-- ─── Seed demo products ────────────────────────────────────────────────────
INSERT IGNORE INTO babi_products (id, name, brand, ref, kind, cat, price, old, rating, reviews, seller_id, seller, delivery, best, description, stock) VALUES
  (1, 'Plaquettes de frein avant', 'Brembo', 'P 61 099', 'brake', 'freinage', 34.90, 41.00, 4.8, 212, 1, 'AutoStock Pro', '48h', 1, 'Pièce haute performance pour un freinage sûr en usage urbain et autoroutier.', 48),
  (2, 'Disque de frein ventilé',   'Bosch',  '0 986 479 R', 'brake', 'freinage', 58.50, NULL, 4.7, 96, 1, 'AutoStock Pro', '24h', 0, NULL, 12),
  (3, 'Filtre à huile',            'MANN-FILTER', 'W 712/95', 'oil', 'filtres', 8.90, NULL, 4.9, 540, 1, 'AutoStock Pro', '48h', 1, NULL, 230),
  (4, 'Filtre à air moteur',       'MANN-FILTER', 'C 30 005', 'filter', 'filtres', 14.20, NULL, 4.6, 188, 1, 'AutoStock Pro', '72h', 0, NULL, 64),
  (5, 'Bougie d\'allumage iridium','NGK', 'IZFR6K-11', 'spark', 'allumage', 9.40, 12.00, 4.8, 320, 1, 'AutoStock Pro', '24h', 1, NULL, 86),
  (6, 'Amortisseur arrière',       'Sachs', '314 875', 'susp', 'suspension', 62.00, NULL, 4.5, 74, 1, 'AutoStock Pro', '72h', 0, NULL, 8),
  (7, 'Batterie 70Ah 760A',        'Varta', 'E44 Blue', 'battery', 'batterie', 109.00, NULL, 4.9, 410, 1, 'AutoStock Pro', '48h', 1, NULL, 22),
  (8, 'Ampoule H7 LED',            'Philips', 'X-treme 11972', 'bulb', 'eclairage', 39.90, NULL, 4.4, 132, 1, 'AutoStock Pro', '24h', 0, NULL, 41);

-- ─── Seed marques ──────────────────────────────────────────────────────────
INSERT IGNORE INTO babi_marques (id, nom) VALUES
  (1,'Peugeot'),(2,'Renault'),(3,'Citroën'),(4,'Volkswagen'),(5,'Toyota'),
  (6,'BMW'),(7,'Mercedes-Benz'),(8,'Ford'),(9,'Opel'),(10,'Audi');

-- ─── Seed modeles ──────────────────────────────────────────────────────────
INSERT IGNORE INTO babi_modeles (id, marque_id, nom, annee_debut, annee_fin) VALUES
  -- Peugeot
  (1,1,'208',2012,2024),(2,1,'308',2007,2024),(3,1,'3008',2009,2024),(4,1,'508',2010,2024),
  -- Renault
  (5,2,'Clio',2005,2024),(6,2,'Mégane',2002,2024),(7,2,'Kadjar',2015,2024),(8,2,'Captur',2013,2024),
  -- Citroën
  (9,3,'C3',2002,2024),(10,3,'C4',2004,2024),(11,3,'C5 Aircross',2018,2024),(12,3,'Berlingo',2008,2024),
  -- Volkswagen
  (13,4,'Golf',2003,2024),(14,4,'Polo',2005,2024),(15,4,'Passat',2005,2024),(16,4,'Tiguan',2007,2024),
  -- Toyota
  (17,5,'Yaris',2005,2024),(18,5,'Corolla',2013,2024),(19,5,'RAV4',2006,2024),
  -- BMW
  (20,6,'Série 1',2004,2024),(21,6,'Série 3',2005,2024),(22,6,'Série 5',2003,2024),
  -- Mercedes-Benz
  (23,7,'Classe A',2004,2024),(24,7,'Classe C',2000,2024),(25,7,'Classe E',2002,2024),
  -- Ford
  (26,8,'Fiesta',2008,2023),(27,8,'Focus',2004,2024),(28,8,'Puma',2019,2024),
  -- Opel
  (29,9,'Corsa',2006,2024),(30,9,'Astra',2004,2024),(31,9,'Mokka',2012,2024),
  -- Audi
  (32,10,'A3',2003,2024),(33,10,'A4',2004,2024),(34,10,'Q3',2011,2024);

-- ─── Seed motorisations ────────────────────────────────────────────────────
INSERT IGNORE INTO babi_motorisations (id, modele_id, annee_debut, annee_fin, nom, carburant) VALUES
  -- Peugeot 208
  (1,1,2012,2019,'1.0 PureTech 65','essence'),(2,1,2015,2024,'1.2 PureTech 100','essence'),
  (3,1,2018,2024,'1.5 BlueHDi 100','diesel'),(4,1,2019,2024,'e-208 136ch','electrique'),
  -- Peugeot 308
  (5,2,2007,2013,'1.6 HDi 90','diesel'),(6,2,2013,2024,'1.2 PureTech 110','essence'),
  (7,2,2014,2020,'1.6 BlueHDi 120','diesel'),(8,2,2021,2024,'1.5 BlueHDi 130','diesel'),
  -- Peugeot 3008
  (9,3,2009,2016,'1.6 HDi 115','diesel'),(10,3,2016,2024,'1.5 BlueHDi 130','diesel'),
  (11,3,2016,2024,'1.6 PureTech 180','essence'),(12,3,2019,2024,'Hybrid4 300','hybride'),
  -- Peugeot 508
  (13,4,2010,2018,'2.0 BlueHDi 150','diesel'),(14,4,2018,2024,'1.5 BlueHDi 130','diesel'),
  (15,4,2018,2024,'1.6 PureTech 180','essence'),
  -- Renault Clio
  (16,5,2005,2014,'1.5 dCi 75','diesel'),(17,5,2013,2024,'1.5 dCi 90','diesel'),
  (18,5,2019,2024,'TCe 100','essence'),(19,5,2020,2024,'E-Tech 140','hybride'),
  -- Renault Mégane
  (20,6,2002,2009,'1.9 dCi 130','diesel'),(21,6,2009,2016,'1.5 dCi 110','diesel'),
  (22,6,2016,2024,'1.5 Blue dCi 115','diesel'),(23,6,2016,2024,'TCe 140','essence'),
  -- Renault Kadjar
  (24,7,2015,2024,'1.5 dCi 115','diesel'),(25,7,2015,2024,'1.3 TCe 140','essence'),
  -- Renault Captur
  (26,8,2013,2019,'1.5 dCi 90','diesel'),(27,8,2019,2024,'1.0 TCe 100','essence'),
  (28,8,2020,2024,'E-Tech 145','hybride'),
  -- Citroën C3
  (29,9,2002,2009,'1.4 HDi 70','diesel'),(30,9,2009,2024,'1.5 BlueHDi 100','diesel'),
  (31,9,2017,2024,'1.2 PureTech 83','essence'),
  -- Citroën C4
  (32,10,2004,2011,'1.6 HDi 92','diesel'),(33,10,2010,2018,'1.6 BlueHDi 100','diesel'),
  (34,10,2020,2024,'1.5 BlueHDi 130','diesel'),(35,10,2021,2024,'ë-C4 136ch','electrique'),
  -- Citroën C5 Aircross
  (36,11,2018,2024,'1.5 BlueHDi 130','diesel'),(37,11,2018,2024,'1.2 PureTech 130','essence'),
  (38,11,2020,2024,'Hybrid 225','hybride'),
  -- Citroën Berlingo
  (39,12,2008,2018,'1.6 BlueHDi 100','diesel'),(40,12,2018,2024,'1.5 BlueHDi 100','diesel'),
  -- Volkswagen Golf
  (41,13,2003,2013,'1.9 TDI 105','diesel'),(42,13,2013,2020,'1.6 TDI 110','diesel'),
  (43,13,2013,2020,'1.4 TSI 125','essence'),(44,13,2020,2024,'2.0 TDI 150','diesel'),
  -- Volkswagen Polo
  (45,14,2005,2009,'1.4 TDI 80','diesel'),(46,14,2009,2017,'1.6 TDI 90','diesel'),
  (47,14,2017,2024,'1.0 TSI 95','essence'),
  -- Volkswagen Passat
  (48,15,2005,2015,'2.0 TDI 140','diesel'),(49,15,2015,2024,'2.0 TDI 150','diesel'),
  (50,15,2015,2024,'1.5 TSI 150','essence'),
  -- Volkswagen Tiguan
  (51,16,2007,2016,'2.0 TDI 140','diesel'),(52,16,2016,2024,'2.0 TDI 150','diesel'),
  (53,16,2016,2024,'1.5 TSI 150','essence'),
  -- Toyota Yaris
  (54,17,2005,2011,'1.4 D-4D 90','diesel'),(55,17,2011,2020,'1.5 Hybrid 100','hybride'),
  (56,17,2020,2024,'1.5 Hybrid 116','hybride'),
  -- Toyota Corolla
  (57,18,2013,2019,'1.6 Valvematic 132','essence'),(58,18,2019,2024,'1.8 Hybrid 122','hybride'),
  (59,18,2019,2024,'2.0 Hybrid 184','hybride'),
  -- Toyota RAV4
  (60,19,2006,2013,'2.2 D-4D 150','diesel'),(61,19,2013,2018,'2.0 D-4D 124','diesel'),
  (62,19,2019,2024,'2.5 Hybrid 218','hybride'),
  -- BMW Série 1
  (63,20,2004,2011,'118d 143','diesel'),(64,20,2011,2019,'116d 116','diesel'),
  (65,20,2019,2024,'118i 136','essence'),
  -- BMW Série 3
  (66,21,2005,2012,'320d 163','diesel'),(67,21,2012,2019,'318d 143','diesel'),
  (68,21,2019,2024,'320d 190','diesel'),(69,21,2019,2024,'330e 292','hybride'),
  -- BMW Série 5
  (70,22,2003,2010,'520d 177','diesel'),(71,22,2010,2017,'520d 184','diesel'),
  (72,22,2017,2024,'520d 190','diesel'),
  -- Mercedes Classe A
  (73,23,2004,2013,'A 180 CDI 109','diesel'),(74,23,2013,2018,'A 200 CDI 136','diesel'),
  (75,23,2018,2024,'A 180 d 116','diesel'),(76,23,2018,2024,'A 200 163','essence'),
  -- Mercedes Classe C
  (77,24,2000,2007,'C 220 CDI 143','diesel'),(78,24,2007,2014,'C 220 CDI 170','diesel'),
  (79,24,2014,2021,'C 220 d 170','diesel'),(80,24,2021,2024,'C 220 d 200','diesel'),
  -- Mercedes Classe E
  (81,25,2002,2009,'E 220 CDI 170','diesel'),(82,25,2009,2016,'E 220 CDI 170','diesel'),
  (83,25,2016,2024,'E 220 d 194','diesel'),
  -- Ford Fiesta
  (84,26,2008,2013,'1.4 TDCi 68','diesel'),(85,26,2013,2023,'1.5 TDCi 85','diesel'),
  (86,26,2017,2023,'1.0 EcoBoost 100','essence'),
  -- Ford Focus
  (87,27,2004,2011,'1.6 TDCi 109','diesel'),(88,27,2011,2019,'1.5 TDCi 120','diesel'),
  (89,27,2019,2024,'1.5 EcoBlue 120','diesel'),
  -- Ford Puma
  (90,28,2019,2024,'1.0 EcoBoost 125','essence'),(91,28,2019,2024,'1.5 EcoBlue 120','diesel'),
  -- Opel Corsa
  (92,29,2006,2015,'1.3 CDTI 75','diesel'),(93,29,2015,2019,'1.3 CDTI 95','diesel'),
  (94,29,2019,2024,'1.2 Turbo 100','essence'),(95,29,2020,2024,'e-Corsa 136','electrique'),
  -- Opel Astra
  (96,30,2004,2010,'1.9 CDTI 120','diesel'),(97,30,2010,2015,'2.0 CDTI 165','diesel'),
  (98,30,2015,2022,'1.6 CDTI 136','diesel'),(99,30,2022,2024,'1.5 Turbo D 130','diesel'),
  -- Opel Mokka
  (100,31,2012,2021,'1.6 CDTI 136','diesel'),(101,31,2021,2024,'1.2 Turbo 130','essence'),
  (102,31,2021,2024,'e-Mokka 136','electrique'),
  -- Audi A3
  (103,32,2003,2013,'1.9 TDI 105','diesel'),(104,32,2013,2020,'2.0 TDI 150','diesel'),
  (105,32,2013,2020,'1.4 TFSI 150','essence'),(106,32,2020,2024,'30 TDI 116','diesel'),
  -- Audi A4
  (107,33,2004,2008,'2.0 TDI 140','diesel'),(108,33,2008,2016,'2.0 TDI 150','diesel'),
  (109,33,2016,2024,'2.0 TDI 163','diesel'),(110,33,2016,2024,'2.0 TFSI 190','essence'),
  -- Audi Q3
  (111,34,2011,2019,'2.0 TDI 150','diesel'),(112,34,2019,2024,'35 TDI 150','diesel'),
  (113,34,2019,2024,'35 TFSI 150','essence');

-- ─── Avis produits ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_reviews (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  product_id  INT NOT NULL,
  user_id     INT NOT NULL,
  rating      TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment     TEXT,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES babi_products(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES babi_users(id)    ON DELETE CASCADE,
  UNIQUE KEY uq_review (product_id, user_id)
);

-- ─── Messages (chat vendeur/acheteur) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_messages (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  order_id    INT NOT NULL,
  sender_id   INT NOT NULL,
  receiver_id INT NOT NULL,
  body        TEXT NOT NULL,
  read_at     DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id)    REFERENCES babi_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (sender_id)   REFERENCES babi_users(id),
  FOREIGN KEY (receiver_id) REFERENCES babi_users(id)
);

-- ─── Retours produits ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_returns (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  order_id    INT NOT NULL,
  user_id     INT NOT NULL,
  reason      VARCHAR(100) NOT NULL,
  description TEXT,
  status      ENUM('pending','approved','rejected','refunded') NOT NULL DEFAULT 'pending',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES babi_orders(id),
  FOREIGN KEY (user_id)  REFERENCES babi_users(id)
);

-- ─── Alertes prix ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_price_alerts (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  product_id  INT NOT NULL,
  target_price DECIMAL(10,2) NOT NULL,
  triggered   TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)    REFERENCES babi_users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES babi_products(id) ON DELETE CASCADE
);

-- ─── Alertes stock vendeur ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_stock_alerts (
  product_id  INT NOT NULL PRIMARY KEY,
  threshold   INT NOT NULL DEFAULT 5,
  notified_at DATETIME NULL,
  FOREIGN KEY (product_id) REFERENCES babi_products(id) ON DELETE CASCADE
);


-- ─── Push notifications ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS babi_push_tokens (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    token      VARCHAR(255) NOT NULL,
    platform   ENUM('android','ios','web') NOT NULL DEFAULT 'android',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_token (token),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS babi_notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NULL COMMENT 'NULL = broadcast',
    type       ENUM('order_status','promo','alert','new_order') NOT NULL,
    title      VARCHAR(255) NOT NULL,
    body       TEXT NOT NULL,
    data       JSON NULL,
    sent_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
