CREATE DATABASE IF NOT EXISTS welms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE welms;

CREATE TABLE IF NOT EXISTS welms_applications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom_entreprise VARCHAR(255) NOT NULL,
    ville VARCHAR(120) NULL,
    province VARCHAR(120) NULL,
    code_postal VARCHAR(30) NULL,
    courriel_entreprise VARCHAR(190) NOT NULL,
    type_entreprise VARCHAR(190) NULL,
    etablie_depuis VARCHAR(50) NULL,
    mode_paiement VARCHAR(190) NULL,
    livrera VARCHAR(255) NULL,
    ville_2 VARCHAR(120) NULL,
    province_2 VARCHAR(120) NULL,
    code_postal_2 VARCHAR(30) NULL,
    tel_2 VARCHAR(60) NULL,
    courriel_2 VARCHAR(190) NULL,
    resp_cf VARCHAR(255) NOT NULL,
    resp_achats VARCHAR(255) NULL,
    resp_tech VARCHAR(255) NULL,
    president VARCHAR(255) NULL,
    nb_employes INT UNSIGNED NULL,
    type_industrie VARCHAR(190) NULL,
    ref1_nom VARCHAR(255) NULL,
    ref1_personne VARCHAR(255) NULL,
    ref1_tel VARCHAR(60) NULL,
    ref2_nom VARCHAR(255) NULL,
    ref2_personne VARCHAR(255) NULL,
    ref2_tel VARCHAR(60) NULL,
    ref3 VARCHAR(255) NULL,
    ref4 VARCHAR(255) NULL,
    ref1_2 VARCHAR(255) NULL,
    ref2_2 VARCHAR(255) NULL,
    ref1_3 VARCHAR(255) NULL,
    ref2_3 VARCHAR(255) NULL,
    date_signature DATE NOT NULL,
    nom_entreprise_aut VARCHAR(255) NULL,
    nom_signataire VARCHAR(190) NOT NULL,
    titre_signataire VARCHAR(190) NULL,
    adresse_fact VARCHAR(255) NULL,
    adresse_diff VARCHAR(255) NULL,
    tel1 VARCHAR(60) NULL,
    fax1 VARCHAR(60) NULL,
    exotaxes ENUM('oui', 'non') NULL,
    text3 TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE INDEX IF NOT EXISTS idx_welms_app_created_at ON welms_applications (created_at);
CREATE INDEX IF NOT EXISTS idx_welms_app_nom_entreprise ON welms_applications (nom_entreprise);

CREATE TABLE IF NOT EXISTS admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
