-- === Session options (an toan, cap quyen theo session) ===
SET NAMES utf8mb4;
SET time_zone = '+09:00';
SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Nếu chạy lại nhiều lần, xóa view trước để tránh lỗi "exists"
DROP VIEW IF EXISTS v_thong_ke_tra_cuu_7d;

-- =============== NGUOI DUNG (Admin) ======================
CREATE TABLE IF NOT EXISTS nguoi_dung (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email             VARCHAR(190) NOT NULL,
  ho_ten            VARCHAR(120) NULL,
  vai_tro           ENUM('admin') NOT NULL DEFAULT 'admin',
  mat_khau          VARCHAR(255) NULL,       -- NULL khi khoi tao, bat buoc doi mat khau
  bat_buoc_doi_mk   TINYINT(1) NOT NULL DEFAULT 1,
  trang_thai        ENUM('hoat_dong','tam_khoa') NOT NULL DEFAULT 'hoat_dong',
  tao_luc           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cap_nhat_luc      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_nguoi_dung_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== PHIEN DANG NHAP =========================
CREATE TABLE IF NOT EXISTS phien (
  id              CHAR(64) NOT NULL,
  nguoi_dung_id   BIGINT UNSIGNED NOT NULL,
  ip              VARCHAR(45) NULL,
  ua              VARCHAR(255) NULL,
  het_han_luc     DATETIME NOT NULL,
  tao_luc         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_phien_nguoi (nguoi_dung_id),
  CONSTRAINT fk_phien_nguoi FOREIGN KEY (nguoi_dung_id)
    REFERENCES nguoi_dung(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== CAU HINH HE THONG =======================
CREATE TABLE IF NOT EXISTS cau_hinh (
  khoa            VARCHAR(64) NOT NULL,
  gia_tri         TEXT NULL,
  mo_ta           VARCHAR(255) NULL,
  cap_nhat_luc    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (khoa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== NHAT KY TRA CUU (PUBLIC) ================
CREATE TABLE IF NOT EXISTS nhat_ky_tra_cuu (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  so_dien_thoai       VARCHAR(32) NOT NULL,
  so_dt_chuan         VARCHAR(32) AS (REGEXP_REPLACE(so_dien_thoai, '[^0-9]', '')) STORED,
  ip                  VARCHAR(45) NULL,
  ua                  VARCHAR(255) NULL,
  ket_qua_so_don      INT NOT NULL DEFAULT 0,
  tao_luc             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tra_cuu_dt_chuan (so_dt_chuan),
  KEY idx_tra_cuu_time (tao_luc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== TAP TIN TAI LEN =========================
CREATE TABLE IF NOT EXISTS tap_tin_tai_len (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loai                ENUM('excel','json') NOT NULL,
  ten_hien_thi        VARCHAR(200) NOT NULL,
  tep_duong_dan       VARCHAR(500) NOT NULL,
  tep_loai_mime       VARCHAR(120) NULL,
  kich_thuoc          BIGINT UNSIGNED NULL,
  tep_hash_sha256     CHAR(64) NULL,
  trang_thai          ENUM('hop_le','loi') NOT NULL DEFAULT 'hop_le',
  ghi_chu             VARCHAR(255) NULL,
  nguoi_dung_id       BIGINT UNSIGNED NULL,
  tao_luc             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tt_loai (loai),
  KEY idx_tt_user (nguoi_dung_id),
  CONSTRAINT fk_tt_user FOREIGN KEY (nguoi_dung_id)
    REFERENCES nguoi_dung(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== EXCEL PHAN VUNG =========================
CREATE TABLE IF NOT EXISTS excel_phan_vung (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tap_tin_id          BIGINT UNSIGNED NOT NULL,
  hang_bat_dau        INT NOT NULL CHECK (hang_bat_dau >= 1),
  hang_ket_thuc       INT NOT NULL CHECK (hang_ket_thuc >= hang_bat_dau),
  mo_ta               VARCHAR(255) NULL,
  tao_luc             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pv_tap_tin (tap_tin_id),
  CONSTRAINT fk_pv_tap_tin FOREIGN KEY (tap_tin_id)
    REFERENCES tap_tin_tai_len(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== THECBE DONG =============================
CREATE TABLE IF NOT EXISTS thecbe_dong (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tap_tin_id          BIGINT UNSIGNED NOT NULL,
  hang_tu             INT NOT NULL CHECK (hang_tu >= 1),
  hang_den            INT NOT NULL CHECK (hang_den >= hang_tu),
  so_dien_thoai       VARCHAR(32) NOT NULL,
  so_dt_chuan         VARCHAR(32) AS (REGEXP_REPLACE(so_dien_thoai, '[^0-9]', '')) STORED,
  ma_thecbe           VARCHAR(128) NOT NULL,
  ghi_chu             VARCHAR(255) NULL,
  tao_luc             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tcb_tap_tin (tap_tin_id),
  KEY idx_tcb_sdt (so_dt_chuan),
  CONSTRAINT fk_tcb_tap_tin FOREIGN KEY (tap_tin_id)
    REFERENCES tap_tin_tai_len(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== DON HANG CACHE ==========================
CREATE TABLE IF NOT EXISTS don_hang_cache (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  so_dien_thoai         VARCHAR(32) NOT NULL,
  so_dt_chuan           VARCHAR(32) AS (REGEXP_REPLACE(so_dien_thoai, '[^0-9]', '')) STORED,
  ngay_thang            DATE NULL,
  so_luong_gop          INT NULL CHECK (so_luong_gop IS NULL OR so_luong_gop >= 0),
  ten_khach             VARCHAR(200) NULL,
  ma_so_ao              VARCHAR(120) NULL,
  so_tien_ao            DECIMAL(14,2) NULL CHECK (so_tien_ao IS NULL OR so_tien_ao >= 0),
  phi_van_chuyen        DECIMAL(14,2) NULL CHECK (phi_van_chuyen IS NULL OR phi_van_chuyen >= 0),
  tong_tien             DECIMAL(14,2) NULL CHECK (tong_tien IS NULL OR tong_tien >= 0),
  ghi_chu               TEXT NULL,
  tt_thanh_toan         VARCHAR(120) NULL,
  tt_nhan_tin           VARCHAR(120) NULL,
  dia_chi               TEXT NULL,
  tt_gui_hang           VARCHAR(120) NULL,
  tt_cho_gop            VARCHAR(120) NULL,
  hien_trang            VARCHAR(120) NULL,
  xu_ly_thecbe          VARCHAR(120) NULL,
  anh_thecbe            VARCHAR(500) NULL,
  ma_thecbe             VARCHAR(128) NULL,
  ket_qua_xu_ly         VARCHAR(200) NULL,
  json_goc              JSON NULL,
  nguon                 ENUM('gas_json','upload') NOT NULL DEFAULT 'gas_json',
  cap_nhat_luc          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  tao_luc               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

ALTER TABLE don_hang_cache
  ADD INDEX idx_sdt_ttl (so_dt_chuan, cap_nhat_luc);


  -- Tao cot sinh de dam bao UNIQUE khi ma_so_ao NULL
  ma_so_ao_nn           VARCHAR(120) AS (IFNULL(ma_so_ao,'')) STORED,

  PRIMARY KEY (id),
  KEY idx_dh_sdt (so_dt_chuan),
  KEY idx_dh_ngay (ngay_thang),
  KEY idx_dh_ma_ao (ma_so_ao),
  UNIQUE KEY uq_dh_chong_trung (so_dt_chuan, ngay_thang, ma_so_ao_nn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== NHAT KY HE THONG ========================
CREATE TABLE IF NOT EXISTS nhat_ky_he_thong (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loai            VARCHAR(50) NOT NULL,   -- 'ADMIN_LOGIN','UPLOAD_EXCEL','EXPORT_JSON','TOGGLE_MAINT',...
  hanh_dong       VARCHAR(120) NOT NULL,
  du_lieu_json    JSON NULL,
  nguoi_dung_id   BIGINT UNSIGNED NULL,
  tao_luc         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_log_loai (loai),
  KEY idx_log_user (nguoi_dung_id),
  CONSTRAINT fk_log_user FOREIGN KEY (nguoi_dung_id)
    REFERENCES nguoi_dung(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============== VIEW thong ke 7 ngay ====================
CREATE OR REPLACE VIEW v_thong_ke_tra_cuu_7d AS
SELECT
  so_dt_chuan,
  COUNT(*) AS so_lan,
  MIN(tao_luc) AS lan_dau,
  MAX(tao_luc) AS lan_cuoi
FROM nhat_ky_tra_cuu
WHERE tao_luc >= (CURRENT_TIMESTAMP - INTERVAL 7 DAY)
GROUP BY so_dt_chuan;

-- =============== SEED cau hinh ===========================
INSERT INTO cau_hinh (khoa, gia_tri, mo_ta) VALUES
  ('site_title','Havy.me - Tra cuu don','Tieu de trang chu')
ON DUPLICATE KEY UPDATE gia_tri=VALUES(gia_tri), mo_ta=VALUES(mo_ta);

INSERT INTO cau_hinh (khoa, gia_tri, mo_ta) VALUES
  ('bao_tri_bat','0','0=tat, 1=bat')
ON DUPLICATE KEY UPDATE gia_tri=VALUES(gia_tri), mo_ta=VALUES(mo_ta);

INSERT INTO cau_hinh (khoa, gia_tri, mo_ta) VALUES
  ('bao_tri_thong_bao','He thong dang bao tri, vui long quay lai sau.','Noi dung thong bao')
ON DUPLICATE KEY UPDATE gia_tri=VALUES(gia_tri), mo_ta=VALUES(mo_ta);

INSERT INTO cau_hinh (khoa, gia_tri, mo_ta) VALUES
  ('json_het_han_giay','600','Thoi gian cache don_hang_cache (giay)')
ON DUPLICATE KEY UPDATE gia_tri=VALUES(gia_tri), mo_ta=VALUES(mo_ta);

-- =============== SEED admin ==============================
INSERT INTO nguoi_dung (email, ho_ten, mat_khau, bat_buoc_doi_mk, trang_thai)
VALUES ('admin@local','Quan tri',NULL,1,'hoat_dong')
ON DUPLICATE KEY UPDATE ho_ten=VALUES(ho_ten);