-- Fecly — schéma MySQL / MariaDB pour hébergement OVH mutualisé.
-- À importer via le Manager OVH (phpMyAdmin) sur la base créée pour le projet.

CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prenom          VARCHAR(255)      NOT NULL,
    nom             VARCHAR(255)      NOT NULL,
    nom_entreprise  VARCHAR(255)      NOT NULL,
    email           VARCHAR(255)      NOT NULL,
    password_hash   VARCHAR(255)      NOT NULL,
    created_at      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED      NOT NULL,
    token_hash  CHAR(64)          NOT NULL,
    created_at  DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  DATETIME          NOT NULL,
    UNIQUE KEY uniq_sessions_token (token_hash),
    CONSTRAINT fk_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_sessions_user ON sessions(user_id);

CREATE TABLE IF NOT EXISTS dossiers (
    id                              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id                         INT UNSIGNED      NOT NULL,
    nom                             VARCHAR(255)      NOT NULL,
    siren                           VARCHAR(9)        NOT NULL,
    date_cloture                    DATE              NULL,
    journal_code                    VARCHAR(20)       NOT NULL DEFAULT 'BQ',
    journal_lib                     VARCHAR(255)      NOT NULL DEFAULT 'Banque',
    compte_banque                   VARCHAR(20)       NOT NULL DEFAULT '512000',
    compte_banque_lib               VARCHAR(255)      NOT NULL DEFAULT 'Banque',
    compte_contrepartie_credit      VARCHAR(20)       NOT NULL DEFAULT '706000',
    compte_contrepartie_credit_lib  VARCHAR(255)      NOT NULL DEFAULT 'Prestations de services',
    compte_contrepartie_debit       VARCHAR(20)       NOT NULL DEFAULT '606100',
    compte_contrepartie_debit_lib   VARCHAR(255)      NOT NULL DEFAULT 'Fournitures non stockables',
    next_ecriture_num               INT UNSIGNED      NOT NULL DEFAULT 1,
    created_at                      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_dossiers_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_dossiers_user ON dossiers(user_id);

CREATE TABLE IF NOT EXISTS exports (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dossier_id        INT UNSIGNED      NOT NULL,
    file_name         VARCHAR(255)      NOT NULL,
    line_count        INT UNSIGNED      NOT NULL DEFAULT 0,
    transaction_count INT UNSIGNED      NOT NULL DEFAULT 0,
    content           LONGTEXT          NOT NULL,
    generated_at      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_exports_dossier
        FOREIGN KEY (dossier_id) REFERENCES dossiers(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_exports_dossier ON exports(dossier_id);
