-- ==========================================================================
--  MONTANTE VIP — Schéma de base de données MySQL / MariaDB
--  Encodage : utf8mb4 (support complet des emojis et accents)
--  Importez ce fichier dans votre base (phpMyAdmin sur o2switch, ou CLI) :
--      mysql -u VOTRE_USER -p VOTRE_BASE < database.sql
-- ==========================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

-- --------------------------------------------------------------------------
--  Table : users
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`              VARCHAR(80)     NOT NULL,
  `email`             VARCHAR(190)    NOT NULL,
  `password_hash`     VARCHAR(255)    NOT NULL,
  `role`              ENUM('user','admin') NOT NULL DEFAULT 'user',
  `is_active`         TINYINT(1)      NOT NULL DEFAULT 1,
  `is_blocked`        TINYINT(1)      NOT NULL DEFAULT 0,
  `stripe_customer_id` VARCHAR(255)   DEFAULT NULL,
  `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`        DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_stripe_customer` (`stripe_customer_id`),
  KEY `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : subscriptions  (droits d'accès VIP : passes & abonnements)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`                BIGINT UNSIGNED NOT NULL,
  `plan_type`              VARCHAR(40)     NOT NULL,
  `billing_mode`           ENUM('legacy','montante_cycle') NOT NULL DEFAULT 'legacy',
  `stripe_customer_id`     VARCHAR(255)    DEFAULT NULL,
  `stripe_subscription_id` VARCHAR(255)    DEFAULT NULL,
  `stripe_payment_method_id` VARCHAR(255)  DEFAULT NULL,
  `current_montante_id`    BIGINT UNSIGNED DEFAULT NULL,
  `last_billed_montante_id` BIGINT UNSIGNED DEFAULT NULL,
  `status`                 VARCHAR(30)     NOT NULL DEFAULT 'active',
  `started_at`             DATETIME        DEFAULT NULL,
  `expires_at`             DATETIME        DEFAULT NULL,
  `canceled_at`            DATETIME        DEFAULT NULL,
  `cancel_at_period_end`   TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`             TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`             TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sub_user` (`user_id`),
  KEY `idx_sub_status` (`status`),
  KEY `idx_sub_expires` (`expires_at`),
  KEY `idx_sub_stripe` (`stripe_subscription_id`),
  KEY `idx_sub_billing_mode` (`billing_mode`),
  KEY `idx_sub_current_montante` (`current_montante_id`),
  CONSTRAINT `fk_sub_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : payments  (historique des transactions)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`            BIGINT UNSIGNED DEFAULT NULL,
  `plan_type`          VARCHAR(40)     NOT NULL,
  `montante_id`        BIGINT UNSIGNED DEFAULT NULL,
  `amount_cents`       INT UNSIGNED    NOT NULL DEFAULT 0,
  `currency`           VARCHAR(10)     NOT NULL DEFAULT 'eur',
  `status`             VARCHAR(30)     NOT NULL DEFAULT 'paid',
  `stripe_customer_id` VARCHAR(255)    DEFAULT NULL,
  `stripe_payment_id`  VARCHAR(255)    DEFAULT NULL,
  `stripe_session_id`  VARCHAR(255)    DEFAULT NULL,
  `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pay_user` (`user_id`),
  KEY `idx_pay_session` (`stripe_session_id`),
  KEY `idx_pay_payment` (`stripe_payment_id`),
  KEY `idx_pay_montante` (`montante_id`),
  CONSTRAINT `fk_pay_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : montantes  (une série de paliers ; suit le résultat global)
--  status : 'active' (en cours), 'won' (terminée gagnante), 'lost' (perdue)
--  result_amount_cents : gains du dernier palier gagné (si terminée gagnante)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `montantes` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `status`               ENUM('active','won','lost') NOT NULL DEFAULT 'active',
  `mode`                 ENUM('manual','ai_auto') NOT NULL DEFAULT 'manual',
  `start_amount_cents`   INT UNSIGNED    NOT NULL DEFAULT 1000,
  `current_amount_cents` INT UNSIGNED    NOT NULL DEFAULT 1000,
  `target_multiplier`    DECIMAL(8,2)    NOT NULL DEFAULT 10.00,
  `target_amount_cents`  INT UNSIGNED    DEFAULT NULL,
  `max_paliers`          INT UNSIGNED    NOT NULL DEFAULT 12,
  `next_palier_number`   INT UNSIGNED    NOT NULL DEFAULT 1,
  `result_amount_cents`  INT UNSIGNED    DEFAULT NULL,
  `automation_status`    ENUM('idle','selecting','waiting_result','billing','error') NOT NULL DEFAULT 'idle',
  `automation_error`     TEXT            DEFAULT NULL,
  `started_at`           DATETIME        DEFAULT NULL,
  `ended_at`             DATETIME        DEFAULT NULL,
  `created_at`           TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_montante_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : pronostics  (un palier de montante)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pronostics` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `montante_id`    BIGINT UNSIGNED DEFAULT NULL,
  `palier_number`  INT UNSIGNED    NOT NULL,
  `match_name`     VARCHAR(190)    NOT NULL,
  `event_datetime` DATETIME        NOT NULL,
  `prediction`     VARCHAR(255)    NOT NULL,
  `odds`           DECIMAL(6,2)    NOT NULL,
  `stake`          DECIMAL(10,2)   NOT NULL,
  `status`         ENUM('pending','won','lost','void') NOT NULL DEFAULT 'pending',
  `source`         ENUM('admin','ai') NOT NULL DEFAULT 'admin',
  `external_event_id` VARCHAR(190)  DEFAULT NULL,
  `sport_key`      VARCHAR(80)      DEFAULT NULL,
  `bookmaker_key`  VARCHAR(80)      DEFAULT NULL,
  `market_key`     VARCHAR(80)      DEFAULT NULL,
  `outcome_name`   VARCHAR(190)     DEFAULT NULL,
  `outcome_point`  DECIMAL(8,2)     DEFAULT NULL,
  `ai_confidence`  DECIMAL(5,2)     DEFAULT NULL,
  `ai_rationale`   TEXT             DEFAULT NULL,
  `provider_payload` MEDIUMTEXT     DEFAULT NULL,
  `admin_note`     TEXT            DEFAULT NULL,
  `is_published`   TINYINT(1)      NOT NULL DEFAULT 0,
  `published_at`   DATETIME        DEFAULT NULL,
  `resolved_at`    DATETIME        DEFAULT NULL,
  `result_checked_at` DATETIME     DEFAULT NULL,
  `live_home_score` INT UNSIGNED   DEFAULT NULL,
  `live_away_score` INT UNSIGNED   DEFAULT NULL,
  `live_score_completed` TINYINT(1) NOT NULL DEFAULT 0,
  `live_score_last_update` DATETIME DEFAULT NULL,
  `live_score_checked_at` DATETIME DEFAULT NULL,
  `created_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pro_published` (`is_published`),
  KEY `idx_pro_palier` (`palier_number`),
  KEY `idx_pro_montante` (`montante_id`),
  KEY `idx_pro_external_event` (`external_event_id`),
  KEY `idx_pro_status_event` (`status`, `event_datetime`),
  CONSTRAINT `fk_pro_montante` FOREIGN KEY (`montante_id`) REFERENCES `montantes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : app_settings  (configuration admin editable)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `app_settings` (
  `key_name`   VARCHAR(80)  NOT NULL,
  `value_text` TEXT         DEFAULT NULL,
  `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : automation_runs  (journal technique du moteur autonome)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `automation_runs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `run_type`   VARCHAR(60)     NOT NULL,
  `status`     ENUM('started','success','error','skipped') NOT NULL DEFAULT 'started',
  `message`    TEXT            DEFAULT NULL,
  `details`    MEDIUMTEXT      DEFAULT NULL,
  `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_automation_runs_type` (`run_type`),
  KEY `idx_automation_runs_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : montante_billings
--  Une ligne par tentative de renouvellement utilisateur/montante. La clé
--  unique empêche tout double prélèvement pour une même montante.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `montante_billings` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`            BIGINT UNSIGNED NOT NULL,
  `montante_id`        BIGINT UNSIGNED NOT NULL,
  `renewal_from_montante_id` BIGINT UNSIGNED DEFAULT NULL,
  `amount_cents`       INT UNSIGNED    NOT NULL,
  `currency`           VARCHAR(10)     NOT NULL DEFAULT 'eur',
  `status`             ENUM('pending','paid','failed','skipped') NOT NULL DEFAULT 'pending',
  `stripe_customer_id` VARCHAR(255)    DEFAULT NULL,
  `stripe_payment_method_id` VARCHAR(255) DEFAULT NULL,
  `stripe_payment_intent_id` VARCHAR(255) DEFAULT NULL,
  `error_code`         VARCHAR(120)    DEFAULT NULL,
  `error_message`      TEXT            DEFAULT NULL,
  `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_billing_user_montante` (`user_id`, `montante_id`),
  KEY `idx_billing_status` (`status`),
  KEY `idx_billing_payment_intent` (`stripe_payment_intent_id`),
  CONSTRAINT `fk_billing_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_billing_montante` FOREIGN KEY (`montante_id`) REFERENCES `montantes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_billing_renewal_from` FOREIGN KEY (`renewal_from_montante_id`) REFERENCES `montantes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : admin_logs  (journal d'audit des actions admin)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id`   BIGINT UNSIGNED DEFAULT NULL,
  `action`     VARCHAR(80)     NOT NULL,
  `details`    TEXT            DEFAULT NULL,
  `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_log_admin` (`admin_id`),
  CONSTRAINT `fk_log_admin` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : password_resets  (réinitialisation de mot de passe)
--  Seul le HASH (SHA-256) du token est stocké, jamais le token en clair.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    BIGINT UNSIGNED NOT NULL,
  `token_hash` CHAR(64)        NOT NULL,
  `expires_at` DATETIME        NOT NULL,
  `used_at`    DATETIME        DEFAULT NULL,
  `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reset_token` (`token_hash`),
  KEY `idx_reset_user` (`user_id`),
  CONSTRAINT `fk_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
--  Table : sessions  (stockage des sessions par express-mysql-session)
--  Créée automatiquement par l'application, fournie ici pour information.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` VARCHAR(128)        NOT NULL,
  `expires`    INT UNSIGNED        NOT NULL,
  `data`       MEDIUMTEXT          DEFAULT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SET foreign_key_checks = 1;

-- --------------------------------------------------------------------------
--  Réglages initiaux de l'automatisation niveau 3
-- --------------------------------------------------------------------------
INSERT INTO `app_settings` (`key_name`, `value_text`) VALUES
  ('automation_enabled', 'false'),
  ('automation_publish_without_admin', 'true'),
  ('montante_pack_price_cents', '1499'),
  ('montante_start_amount_cents', '1000'),
  ('montante_target_multiplier', '10'),
  ('montante_target_amount_cents', '0'),
  ('montante_max_paliers', '12'),
  ('odds_min', '1.25'),
  ('odds_max', '2.10'),
  ('ai_min_confidence', '0.68')
ON DUPLICATE KEY UPDATE `value_text` = VALUES(`value_text`);

-- ==========================================================================
--  PREMIER ADMINISTRATEUR
--  Le plus simple et sûr : utilisez le script fourni qui hache le mot de passe
--      npm run create-admin -- "Nom Admin" admin@exemple.fr "MotDePasseFort"
--  (Voir README.md). Évitez d'insérer un mot de passe en clair ici.
-- ==========================================================================


-- ==========================================================================
--  COMPTE ADMINISTRATEUR PRÉ-CRÉÉ
--  Email    : info@adrisegon.fr
--  Mot de passe : temporaire, communiqué avec le livrable — à changer après la 1re connexion
--  Le mot de passe est stocké haché (bcrypt) — jamais en clair.
-- ==========================================================================
INSERT INTO `users` (`name`, `email`, `password_hash`, `role`, `is_active`, `is_blocked`)
VALUES ('AdriSegon', 'info@adrisegon.fr', '$2a$12$ArtEq8H/eiO7EMjAEF5acujbMn512zDhG9DTc/XQFiUuGOZY3WCkS', 'admin', 1, 0);