/*
 * PROJECT: TITRIX PLATFORM - COMPLETE DATABASE SCHEMA
 * MODULES: 
 * 1. Core Authentication (RBAC)
 * 2. General Parameters (PARAM)
 * 3. Security Management (TITRES)
 * 4. Client Management (CLIENTS)
 * 5. Account Management (COMPTES)
 */

-- Disable Foreign Key checks to allow dropping/creating tables in any order
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. CLEANUP (DROP ALL EXISTING TABLES)
-- ============================================================

-- Core/Auth
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `clients`; -- Platform level clients
DROP TABLE IF EXISTS `roles`;

-- Titrix: Account Mgmt & Correspondence
DROP TABLE IF EXISTS `CORRESP`;
DROP TABLE IF EXISTS `ETIQ_MAT`;
DROP TABLE IF EXISTS `ETIQ_ESP`;
DROP TABLE IF EXISTS `LIGVAL`;
DROP TABLE IF EXISTS `COMPTE`;
DROP TABLE IF EXISTS `CPTIT`;

-- Titrix: Accounting Plans & Natures
DROP TABLE IF EXISTS `NAT_MAT`;
DROP TABLE IF EXISTS `NAT_ESP`;
DROP TABLE IF EXISTS `PLAN_MAT`;
DROP TABLE IF EXISTS `PLAN_ESP`;

-- Titrix: Client Mgmt
DROP TABLE IF EXISTS `CLIENT`; -- Titrix level client details
DROP TABLE IF EXISTS `FORMJURI`;
DROP TABLE IF EXISTS `PROFIL`;
DROP TABLE IF EXISTS `ETAB`;

-- Titrix: Security Mgmt
DROP TABLE IF EXISTS `COURS`;
DROP TABLE IF EXISTS `TITRE`;
DROP TABLE IF EXISTS `HISDEV`;
DROP TABLE IF EXISTS `DEVISE`;
DROP TABLE IF EXISTS `EMET`;
DROP TABLE IF EXISTS `CATTIT`;

-- Titrix: General
DROP TABLE IF EXISTS `PARAM`;

-- ============================================================
-- 2. MODULE: CORE & AUTHENTICATION
-- ============================================================

CREATE TABLE `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE, 
  `permissions` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `clients` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `legal_name` VARCHAR(150) NOT NULL,
  `client_code` VARCHAR(20) NOT NULL UNIQUE,
  `tax_id` VARCHAR(50) NOT NULL,
  `type` ENUM('ISSUER', 'INVESTOR', 'INTERMEDIARY', 'CENTRAL_BANK') NOT NULL,
  `contact_details` JSON DEFAULT NULL,
  `status` ENUM('ACTIVE', 'SUSPENDED', 'PENDING') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_id` INT DEFAULT NULL,
  `role_id` INT NOT NULL,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `last_login` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 3. MODULE: TITRIX GENERAL PARAMETERS
-- ============================================================

CREATE TABLE PARAM (
    Cle_nomenc  CHAR(10) NOT NULL COMMENT 'Clé nomenclature',
    int_lib1    CHAR(30), Val_lib1 VARCHAR(10),
    int_lib2    CHAR(30), Val_lib2 VARCHAR(10),
    int_lib3    CHAR(30), Val_lib3 VARCHAR(10),
    int_mon1    CHAR(30), Val_mon1 VARCHAR(10),
    int_mon2    CHAR(30), Val_mon2 VARCHAR(10),
    int_mon3    CHAR(30), Val_mon3 VARCHAR(10),
    int_tau1    CHAR(30), Val_tau1 VARCHAR(10),
    int_tau2    CHAR(30), Val_tau2 VARCHAR(10),
    int_tau3    CHAR(30), Val_tau3 VARCHAR(10),
    PRIMARY KEY (Cle_nomenc)
) ENGINE=InnoDB;

-- ============================================================
-- 4. MODULE: TITRIX SECURITY MANAGEMENT
-- ============================================================

CREATE TABLE CATTIT (
    categ       CHAR(3) NOT NULL,
    inti        CHAR(30),
    Actif       BOOLEAN,
    dern_pie    DECIMAL(4,0),
    PRIMARY KEY (categ)
) ENGINE=InnoDB;

CREATE TABLE EMET (
    Cod_emet    CHAR(6) NOT NULL,
    Nom         CHAR(15),
    Sigl        CHAR(15),
    Numcli      CHAR(5),
    rai_soc     CHAR(15),
    Form_juri   CHAR(2),
    Dev         CHAR(3),
    Capital     DECIMAL(18,4),
    PRIMARY KEY (Cod_emet)
) ENGINE=InnoDB;

CREATE TABLE DEVISE (
    cod_etab    CHAR(5),
    dev_num     CHAR(3),
    dev_alpha   CHAR(3) NOT NULL,
    taux_change DECIMAL(8,6),
    Nb_deci     DECIMAL(2,0),
    PRIMARY KEY (dev_alpha)
) ENGINE=InnoDB;

CREATE TABLE HISDEV (
    cod_etab    CHAR(5),
    Date_taux   DATE NOT NULL,
    dev_num     CHAR(3),
    dev_alpha   CHAR(3) NOT NULL,
    taux_change DECIMAL(8,6),
    Nb_deci     DECIMAL(2,0),
    PRIMARY KEY (dev_alpha, Date_taux)
) ENGINE=InnoDB;

CREATE TABLE TITRE (
    cat_tit             CHAR(3),
    dct                 CHAR(3),
    codval              CHAR(12) NOT NULL,
    inti                CHAR(30),
    isin                CHAR(12),
    devtit              CHAR(3),
    emet                CHAR(6),
    valnom              DECIMAL(19,4),
    date_emis           DATE,
    date_jouis          DATE,
    duree               DECIMAL(3,0),
    date_anniv          DATE,
    date_ech            DATE,
    mod_calc_com        CHAR(1),
    Mode_remboursement  CHAR(1),
    tx_interet          DECIMAL(8,6),
    tx_vente            DECIMAL(8,6),
    tx_fisc             DECIMAL(8,6),
    Int_precom          BOOLEAN,
    Tacite_recon        BOOLEAN,
    Recap_int           BOOLEAN,
    PRIMARY KEY (codval),
    CONSTRAINT `FK_TITRE_CATTIT` FOREIGN KEY (`cat_tit`) REFERENCES `CATTIT`(`categ`),
    CONSTRAINT `FK_TITRE_EMET` FOREIGN KEY (`emet`) REFERENCES `EMET`(`Cod_emet`)
) ENGINE=InnoDB;

CREATE TABLE COURS (
    DCOURS      DATE NOT NULL,
    Cat_tit     CHAR(3) NOT NULL,
    Codval      CHAR(12) NOT NULL,
    Taux_cours  DECIMAL(19,4),
    cours_jour  DECIMAL(19,4),
    cours_achat DECIMAL(19,4),
    cours_vente DECIMAL(19,4),
    NB_DECI     DECIMAL(4,0),
    PRIMARY KEY (Codval, DCOURS),
    CONSTRAINT `FK_COURS_CATTIT` FOREIGN KEY (`Cat_tit`) REFERENCES `CATTIT`(`categ`),
    CONSTRAINT `FK_COURS_TITRE` FOREIGN KEY (`Codval`) REFERENCES `TITRE`(`codval`)
) ENGINE=InnoDB;

-- ============================================================
-- 5. MODULE: TITRIX CLIENT MANAGEMENT
-- ============================================================

CREATE TABLE ETAB (
    cod_etab    CHAR(5) NOT NULL,
    Sigle       CHAR(10),
    nom         CHAR(60),
    Swift       CHAR(15),
    Bic         CHAR(15),
    Adresse1    CHAR(60),
    Adresse2    CHAR(60),
    Adresse3    CHAR(60),
    Email       CHAR(60),
    Tel         CHAR(10),
    PRIMARY KEY (cod_etab)
) ENGINE=InnoDB;

CREATE TABLE PROFIL (
    profil      CHAR(2) NOT NULL,
    inti        CHAR(60),
    PRIMARY KEY (profil)
) ENGINE=InnoDB;

CREATE TABLE FORMJURI (
    Code_forme_juri CHAR(2) NOT NULL,
    inti            CHAR(60),
    PRIMARY KEY (Code_forme_juri)
) ENGINE=InnoDB;

CREATE TABLE CLIENT (
    numcli      CHAR(7) NOT NULL,
    Cod_etab    CHAR(5) NOT NULL,
    Typ_cli     CHAR(1),
    Profil      CHAR(3),
    Type_piece  CHAR(1),
    Num_piece   CHAR(10),
    rai_soc     CHAR(60),
    sigle       CHAR(30),
    reg_com     CHAR(30),
    Form_juri   CHAR(2),
    dev         CHAR(3),
    capital     DECIMAL(19,4),
    adr1        CHAR(60),
    adr2        CHAR(60),
    Adr3        CHAR(60),
    Numtel      CHAR(10),
    mail        CHAR(30),
    PRIMARY KEY (numcli, Cod_etab),
    FOREIGN KEY (Cod_etab) REFERENCES ETAB(cod_etab),
    FOREIGN KEY (Form_juri) REFERENCES FORMJURI(Code_forme_juri)
) ENGINE=InnoDB;

-- ============================================================
-- 6. MODULE: TITRIX ACCOUNTS & ACCOUNTING PLANS
-- ============================================================

CREATE TABLE PLAN_MAT (
    chapitre    CHAR(6) NOT NULL,
    classe      CHAR(3),
    sous_classe CHAR(15),
    inti_chap   CHAR(60),
    PRIMARY KEY (chapitre)
) ENGINE=InnoDB;

CREATE TABLE PLAN_ESP (
    chapitre    CHAR(6) NOT NULL,
    classe      CHAR(3),
    sous_classe CHAR(15),
    inti_chap   CHAR(60),
    PRIMARY KEY (chapitre)
) ENGINE=InnoDB;

CREATE TABLE NAT_ESP (
    nat_cpt     CHAR(3) NOT NULL,
    Inti_nat    CHAR(60),
    chap_espece CHAR(6),
    inti_chap   CHAR(60),
    PRIMARY KEY (nat_cpt),
    FOREIGN KEY (chap_espece) REFERENCES PLAN_ESP(chapitre)
) ENGINE=InnoDB;

CREATE TABLE NAT_MAT (
    nat_cpt     CHAR(3) NOT NULL,
    Inti_nat    CHAR(60),
    chap_matiere CHAR(6),
    inti_chap   CHAR(60),
    PRIMARY KEY (nat_cpt),
    FOREIGN KEY (chap_matiere) REFERENCES PLAN_MAT(chapitre)
) ENGINE=InnoDB;

CREATE TABLE CPTIT (
    Categ_tit   CHAR(3) NOT NULL,
    numcli      CHAR(5) NOT NULL,
    Cptit       CHAR(11) NOT NULL,
    Etab        CHAR(5),
    Age_Guichet CHAR(5),
    Nom         CHAR(30),
    Ident       CHAR(5),
    Nat_mat     CHAR(3),
    Dev         CHAR(3),
    parraine    CHAR(1),
    Cli_parrain CHAR(5),
    Nat_esp     CHAR(3),
    Cpt_esp_eff CHAR(11),
    Rib         CHAR(2),
    Cpt_esp     CHAR(11),
    Nat_hb      CHAR(3),
    Cpt_hb      CHAR(11),
    PRIMARY KEY (Categ_tit, numcli, Cptit),
    FOREIGN KEY (Nat_mat) REFERENCES NAT_MAT(nat_cpt),
    FOREIGN KEY (Nat_esp) REFERENCES NAT_ESP(nat_cpt)
) ENGINE=InnoDB;

CREATE TABLE COMPTE (
    Numcpt      CHAR(11) NOT NULL,
    Devise      CHAR(3) NOT NULL,
    Typ_cpt     CHAR(1),
    Numcli      CHAR(5),
    Nom         CHAR(60),
    Chap        CHAR(6),
    Cumdeb      DECIMAL(18,4) DEFAULT 0,
    Cumcre      DECIMAL(18,4) DEFAULT 0,
    Solde_ind   DECIMAL(18,4) DEFAULT 0,
    Solde_his   DECIMAL(18,4) DEFAULT 0,
    Solde       DECIMAL(18,4) DEFAULT 0,
    Dern_mvt    DATE,
    PRIMARY KEY (Numcpt, Devise)
) ENGINE=InnoDB;

CREATE TABLE LIGVAL (
    numcli      CHAR(6) NOT NULL,
    cat_tit     CHAR(3) NOT NULL,
    codval      CHAR(12) NOT NULL,
    cptit       CHAR(9) NOT NULL,
    Etab        CHAR(5),
    Chapmat     CHAR(6),
    Solde       DECIMAL(4,0),
    Sens        CHAR(1),
    solde_ind   DECIMAL(4,0),
    sens_ind    CHAR(1),
    der_mvt     DATE,
    PRIMARY KEY (numcli, cat_tit, codval, cptit)
) ENGINE=InnoDB;

CREATE TABLE ETIQ_ESP (
    Etiquette    CHAR(10) NOT NULL,
    Intitule     CHAR(60),
    Nat_cpt      CHAR(3),
    cpt_effectif CHAR(11),
    cpt_miroir   CHAR(11),
    PRIMARY KEY (Etiquette),
    FOREIGN KEY (Nat_cpt) REFERENCES NAT_ESP(nat_cpt)
) ENGINE=InnoDB;

CREATE TABLE ETIQ_MAT (
    Etiquette    CHAR(10) NOT NULL,
    Intitule     CHAR(60),
    Nat_cpt      CHAR(3),
    chapitre     CHAR(6),
    PRIMARY KEY (Etiquette),
    FOREIGN KEY (Nat_cpt) REFERENCES NAT_MAT(nat_cpt),
    FOREIGN KEY (chapitre) REFERENCES PLAN_MAT(chapitre)
) ENGINE=InnoDB;

CREATE TABLE CORRESP (
    Cpt_miroir      CHAR(11) NOT NULL,
    Etab            CHAR(5),
    Typ_Cpt_miroir  CHAR(1),
    agence          CHAR(5),
    devise          CHAR(3),
    cpt_effectif    CHAR(11),
    PRIMARY KEY (Cpt_miroir)
) ENGINE=InnoDB;

-- Re-enable Foreign Key checks
SET FOREIGN_KEY_CHECKS = 1;