-- RNTAA - Schema bază de date
-- Charset: utf8mb4 pentru suport complet Unicode și diacritice

CREATE DATABASE IF NOT EXISTS rntaa CHARACTER SET utf8mb4 COLLATE utf8mb4_romanian_ci;
USE rntaa;

-- ============================================================
-- UTILIZATORI
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    parola VARCHAR(255) NOT NULL,
    rol ENUM('beneficiar', 'furnizor', 'operator') NOT NULL,
    nivel_acces TINYINT DEFAULT NULL COMMENT '0=admin, 1=admin grup, 2=operator, 3=management',
    grup_operational VARCHAR(50) DEFAULT NULL,
    status ENUM('asteptare', 'activ', 'inactiv') NOT NULL DEFAULT 'asteptare',
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rol (rol),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- ============================================================
-- BENEFICIARI
-- ============================================================

CREATE TABLE IF NOT EXISTS beneficiari (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    nume VARCHAR(100) NOT NULL,
    prenume VARCHAR(100) NOT NULL,
    cnp VARBINARY(255) NOT NULL COMMENT 'Criptat cu openssl_encrypt',
    cnp_hash VARCHAR(64) NOT NULL COMMENT 'SHA-256 pentru căutare',
    data_nasterii DATE DEFAULT NULL,
    telefon VARCHAR(20) DEFAULT NULL,
    adresa_strada VARCHAR(255) DEFAULT NULL,
    adresa_numar VARCHAR(20) DEFAULT NULL,
    adresa_bloc VARCHAR(20) DEFAULT NULL,
    adresa_scara VARCHAR(10) DEFAULT NULL,
    adresa_etaj VARCHAR(10) DEFAULT NULL,
    adresa_apartament VARCHAR(10) DEFAULT NULL,
    adresa_localitate VARCHAR(100) DEFAULT NULL,
    adresa_judet VARCHAR(50) DEFAULT NULL,
    adresa_cod_postal VARCHAR(10) DEFAULT NULL,
    grad_handicap ENUM('usor', 'mediu', 'accentuat', 'grav') DEFAULT NULL,
    tip_handicap VARCHAR(100) DEFAULT NULL,
    nivel_educatie VARCHAR(100) DEFAULT NULL,
    statut_ocupational VARCHAR(100) DEFAULT NULL,
    venit VARCHAR(100) DEFAULT NULL,
    gdpr_accepted TINYINT(1) NOT NULL DEFAULT 0,
    gdpr_accepted_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- FURNIZORI
-- ============================================================

CREATE TABLE IF NOT EXISTS furnizori (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    denumire_firma VARCHAR(255) NOT NULL,
    cui VARCHAR(20) NOT NULL,
    nr_reg_comert VARCHAR(30) DEFAULT NULL,
    reprezentant_legal VARCHAR(200) DEFAULT NULL,
    telefon VARCHAR(20) DEFAULT NULL,
    adresa VARCHAR(500) DEFAULT NULL,
    localitate VARCHAR(100) DEFAULT NULL,
    judet VARCHAR(50) DEFAULT NULL,
    cod_postal VARCHAR(10) DEFAULT NULL,
    website VARCHAR(255) DEFAULT NULL,
    descriere TEXT DEFAULT NULL,
    gdpr_accepted TINYINT(1) NOT NULL DEFAULT 0,
    gdpr_accepted_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- CATEGORII RNTAA
-- ============================================================

CREATE TABLE IF NOT EXISTS categorii_rntaa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    denumire VARCHAR(200) NOT NULL,
    descriere TEXT DEFAULT NULL,
    icon VARCHAR(50) DEFAULT NULL COMMENT 'Bootstrap Icons class name',
    ordine INT NOT NULL DEFAULT 0,
    activ TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- PRODUSE
-- ============================================================

CREATE TABLE IF NOT EXISTS produse (
    id INT AUTO_INCREMENT PRIMARY KEY,
    furnizor_id INT NOT NULL,
    categorie_id INT NOT NULL,
    denumire VARCHAR(255) NOT NULL,
    descriere TEXT DEFAULT NULL,
    specificatii TEXT DEFAULT NULL,
    pret DECIMAL(10,2) NOT NULL,
    imagine VARCHAR(255) DEFAULT NULL,
    status ENUM('asteptare', 'activ', 'inactiv', 'respins') NOT NULL DEFAULT 'asteptare',
    validat_de INT DEFAULT NULL,
    validat_la DATETIME DEFAULT NULL,
    motiv_respingere TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (furnizor_id) REFERENCES furnizori(id) ON DELETE CASCADE,
    FOREIGN KEY (categorie_id) REFERENCES categorii_rntaa(id),
    FOREIGN KEY (validat_de) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_categorie (categorie_id)
) ENGINE=InnoDB;

-- ============================================================
-- SESIUNI VOUCHERE
-- ============================================================

CREATE TABLE IF NOT EXISTS sesiuni_vouchere (
    id INT AUTO_INCREMENT PRIMARY KEY,
    denumire VARCHAR(200) NOT NULL,
    cod VARCHAR(50) DEFAULT NULL,
    buget_total DECIMAL(12,2) NOT NULL,
    buget_consumat DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    buget_maxim_voucher DECIMAL(10,2) NOT NULL,
    data_start DATE NOT NULL,
    data_sfarsit DATE DEFAULT NULL,
    status ENUM('activa', 'inchisa', 'suspendata') NOT NULL DEFAULT 'activa',
    creat_de INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (creat_de) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- SOLICITĂRI
-- ============================================================

CREATE TABLE IF NOT EXISTS solicitari (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beneficiar_id INT NOT NULL,
    sesiune_id INT DEFAULT NULL,
    numar_inregistrare VARCHAR(30) NOT NULL UNIQUE,
    status ENUM('depusa', 'deschisa', 'in_actualizare', 'validata', 'anulata', 'finalizata') NOT NULL DEFAULT 'depusa',
    descriere TEXT DEFAULT NULL,
    punctaj INT DEFAULT NULL,
    motiv_anulare TEXT DEFAULT NULL,
    operator_asignat INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (beneficiar_id) REFERENCES beneficiari(id) ON DELETE CASCADE,
    FOREIGN KEY (sesiune_id) REFERENCES sesiuni_vouchere(id) ON DELETE SET NULL,
    FOREIGN KEY (operator_asignat) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_beneficiar (beneficiar_id)
) ENGINE=InnoDB;

-- ============================================================
-- E-VOUCHERE
-- ============================================================

CREATE TABLE IF NOT EXISTS evouchere (
    id INT AUTO_INCREMENT PRIMARY KEY,
    solicitare_id INT NOT NULL UNIQUE,
    sesiune_id INT NOT NULL,
    beneficiar_id INT NOT NULL,
    serie VARCHAR(20) NOT NULL,
    numar INT NOT NULL,
    valoare DECIMAL(10,2) NOT NULL,
    data_emitere DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data_expirare DATE NOT NULL,
    status ENUM('activ', 'utilizat', 'expirat', 'anulat') NOT NULL DEFAULT 'activ',
    emis_de INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (solicitare_id) REFERENCES solicitari(id),
    FOREIGN KEY (sesiune_id) REFERENCES sesiuni_vouchere(id),
    FOREIGN KEY (beneficiar_id) REFERENCES beneficiari(id),
    FOREIGN KEY (emis_de) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uk_serie_numar (serie, numar),
    INDEX idx_status (status),
    INDEX idx_beneficiar (beneficiar_id)
) ENGINE=InnoDB;

-- ============================================================
-- COMENZI
-- ============================================================

CREATE TABLE IF NOT EXISTS comenzi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beneficiar_id INT NOT NULL,
    evoucher_id INT NOT NULL,
    numar_comanda VARCHAR(30) NOT NULL UNIQUE,
    valoare_totala DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    valoare_voucher DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    diferenta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status ENUM('initiata', 'confirmata', 'in_livrare', 'livrata', 'anulata') NOT NULL DEFAULT 'initiata',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (beneficiar_id) REFERENCES beneficiari(id),
    FOREIGN KEY (evoucher_id) REFERENCES evouchere(id),
    INDEX idx_status (status),
    INDEX idx_beneficiar (beneficiar_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS comanda_produse (
    id INT AUTO_INCREMENT PRIMARY KEY,
    comanda_id INT NOT NULL,
    produs_id INT NOT NULL,
    cantitate INT NOT NULL DEFAULT 1,
    pret_unitar DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (comanda_id) REFERENCES comenzi(id) ON DELETE CASCADE,
    FOREIGN KEY (produs_id) REFERENCES produse(id)
) ENGINE=InnoDB;

-- ============================================================
-- DOCUMENTE
-- ============================================================

CREATE TABLE IF NOT EXISTS documente (
    id INT AUTO_INCREMENT PRIMARY KEY,
    solicitare_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size INT NOT NULL,
    uploadat_de INT NOT NULL,
    tip ENUM('justificativ', 'completare', 'operator') NOT NULL DEFAULT 'justificativ',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solicitare_id) REFERENCES solicitari(id) ON DELETE CASCADE,
    FOREIGN KEY (uploadat_de) REFERENCES users(id),
    INDEX idx_solicitare (solicitare_id)
) ENGINE=InnoDB;

-- ============================================================
-- ISTORIC (AUDIT TRAIL)
-- ============================================================

CREATE TABLE IF NOT EXISTS istoric (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entitate_tip ENUM('user', 'solicitare', 'produs', 'voucher', 'comanda', 'sesiune') NOT NULL,
    entitate_id INT NOT NULL,
    actiune VARCHAR(100) NOT NULL,
    detalii TEXT DEFAULT NULL,
    executat_de INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (executat_de) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entitate (entitate_tip, entitate_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- MESAJE SOLICITARE
-- ============================================================

CREATE TABLE IF NOT EXISTS mesaje_solicitare (
    id INT AUTO_INCREMENT PRIMARY KEY,
    solicitare_id INT NOT NULL,
    user_id INT NOT NULL,
    mesaj TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solicitare_id) REFERENCES solicitari(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_solicitare (solicitare_id)
) ENGINE=InnoDB;

-- ============================================================
-- ȘTIRI
-- ============================================================

CREATE TABLE IF NOT EXISTS stiri (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titlu VARCHAR(300) NOT NULL,
    rezumat TEXT DEFAULT NULL,
    continut TEXT NOT NULL,
    imagine VARCHAR(255) DEFAULT NULL,
    autor_id INT DEFAULT NULL,
    publicat TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- PAGINI STATICE
-- ============================================================

CREATE TABLE IF NOT EXISTS pagini (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    titlu VARCHAR(300) NOT NULL,
    continut TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;