-- =====================================================================
-- KYC Platform - kyc.banatconsultanta.ro
-- Schema completă: instituții, useri, interogări, audit, retenție
-- Convenție: EMULATE_PREPARES=true (MySQL shared hosting)
-- =====================================================================

CREATE TABLE institutii (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nume                VARCHAR(255) NOT NULL,
    email_generic       VARCHAR(255) NULL,          -- adresa instituției pt. comunicare rapoarte
    activ               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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE useri (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    institutie_id       INT UNSIGNED NULL,           -- NULL pentru superadmin
    email               VARCHAR(255) NOT NULL UNIQUE,
    parola_hash         VARCHAR(255) NOT NULL,
    nume                VARCHAR(255) NOT NULL,
    rol                 ENUM('superadmin','user') NOT NULL DEFAULT 'user',
    activ               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 (institutie_id) REFERENCES institutii(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interogari (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id                     INT UNSIGNED NOT NULL,
    institutie_id               INT UNSIGNED NOT NULL,   -- denormalizat pt. rapoarte rapide

    -- date de căutare (vor fi golite la anonimizare)
    nume_cautat                 VARCHAR(500) NULL,
    data_nasterii_cautata       DATE NULL,

    -- rezultate (vor fi golite la anonimizare)
    surse_verificate            JSON NULL,     -- ex: {"UN":"ok","EU":"ok","OFAC":"ok","UK":"stale"}
    rezultat_json                JSON NULL,     -- hit-uri brute + scoruri de similaritate
    nr_hituri                   INT UNSIGNED NOT NULL DEFAULT 0,
    raport_pdf_path              VARCHAR(500) NULL,   -- cale în afara webroot; NULL după anonimizare

    -- comunicare raport
    trimis_catre_user           TINYINT(1) NOT NULL DEFAULT 0,
    trimis_catre_institutie     TINYINT(1) NOT NULL DEFAULT 0,
    data_trimitere_institutie   DATETIME NULL,

    -- retenție / anonimizare (GDPR + Legea 129/2019 AML/CFT, 5 ani)
    data_expirare_retentie      DATE NULL,              -- implicit created_at + 5 ani (populat de trigger); editabil manual de superadmin
    stare_retentie              ENUM('activ','notificat','anonimizat') NOT NULL DEFAULT 'activ',
    data_notificare_retentie    DATETIME NULL,          -- când s-a trimis notificarea de expirare
    data_anonimizare            DATETIME NULL,

    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES useri(id),
    FOREIGN KEY (institutie_id) REFERENCES institutii(id),
    INDEX idx_retentie (stare_retentie, data_expirare_retentie),
    INDEX idx_institutie (institutie_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE anonimizari_log (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    interogare_id               INT UNSIGNED NOT NULL,
    superadmin_id                INT UNSIGNED NOT NULL,
    nume_cautat_hash             VARCHAR(64) NULL,   -- SHA-256, doar pt. verificare "a existat"
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (interogare_id) REFERENCES interogari(id),
    FOREIGN KEY (superadmin_id) REFERENCES useri(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NULL,
    actiune             VARCHAR(100) NOT NULL,   -- ex: 'user_login', 'interogare_creata', 'retentie_notificata'
    detalii_json        JSON NULL,
    ip_address          VARCHAR(45) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES useri(id),
    INDEX idx_actiune (actiune, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Trigger: calculează automat data_expirare_retentie la +5 ani, dacă nu e furnizată explicit
DELIMITER $$
CREATE TRIGGER trg_interogari_expirare_retentie
BEFORE INSERT ON interogari
FOR EACH ROW
BEGIN
    IF NEW.data_expirare_retentie IS NULL THEN
        SET NEW.data_expirare_retentie = DATE_ADD(CURDATE(), INTERVAL 5 YEAR);
    END IF;
END$$
DELIMITER ;