/*
    Schema MySQL/MariaDB cho hệ thống Văn Phòng Số (ERP) — chạy trên Orangehost.
    Chỉ lưu dữ liệu cần hiển thị/truy vấn ra ngoài (tương ứng bảng gốc trên Lark Base).
    Dữ liệu nội bộ (Internal Docs, Ghi chú giải pháp AI...) không lưu ở đây, vẫn nằm trên Lark.

    Thứ tự tạo bảng tuân theo phụ thuộc khóa ngoại: Companies -> Users/Contracts -> Assets -> ArchivedTickets
*/

-- =========================================================
-- Companies (tương ứng Bảng 1 "Khách Hàng" trên Lark)
-- =========================================================
CREATE TABLE Companies (
    CompanyID     INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CompanyCode   VARCHAR(50)        NULL,           -- Mã KH (Lark)
    CompanyName   VARCHAR(255)       NOT NULL,        -- Tên KH
    TaxCode       VARCHAR(20)        NOT NULL,        -- Mã số thuế
    Email         VARCHAR(255)       NULL,
    LarkRecordID  VARCHAR(100)       NULL,            -- Liên kết ngược về record gốc trên Lark
    CreatedAt     DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_Companies_TaxCode UNIQUE (TaxCode),
    CONSTRAINT UQ_Companies_CompanyCode UNIQUE (CompanyCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Users (tài khoản đăng nhập Magic Link — nhân viên nội bộ + khách hàng)
-- =========================================================
CREATE TABLE Users (
    UserID        INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    FullName      VARCHAR(255)       NOT NULL,
    Email         VARCHAR(255)       NOT NULL,
    Role          VARCHAR(50)        NOT NULL DEFAULT 'KhachHang',
    CompanyID     INT                NULL,
    -- Lưu SHA-256 hash (64 ký tự hex) của token thật, KHÔNG lưu token thô.
    -- Token thô chỉ tồn tại trong email/link gửi cho user, không bao giờ ghi vào DB.
    MagicToken    CHAR(64)           NULL,
    TokenExpiry   DATETIME           NULL,
    CreatedAt     DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_Users_Email UNIQUE (Email),
    CONSTRAINT CK_Users_Role CHECK (Role IN ('Admin', 'NhanVien', 'KhachHang')),
    CONSTRAINT FK_Users_Company FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IX_Users_CompanyID ON Users(CompanyID);
-- MySQL không hỗ trợ filtered/partial index (WHERE) như SQL Server — tạo index đầy đủ trên cột.
CREATE INDEX IX_Users_MagicToken ON Users(MagicToken);

-- =========================================================
-- Contracts (tương ứng Bảng 2 "Hợp Đồng" trên Lark)
-- =========================================================
CREATE TABLE Contracts (
    ContractID     INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ContractCode   VARCHAR(50)        NOT NULL,        -- Mã HĐ
    CompanyID      INT                NOT NULL,
    SignedDate     DATE               NULL,            -- Ngày Ký
    ExpiryDate     DATE               NOT NULL,        -- Ngày Hết Hạn
    LarkFileToken  VARCHAR(255)       NULL,            -- Token file đính kèm trên Lark (file gốc vẫn ở Lark)
    CreatedAt      DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_Contracts_ContractCode UNIQUE (ContractCode),
    CONSTRAINT FK_Contracts_Company FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IX_Contracts_CompanyID ON Contracts(CompanyID);
CREATE INDEX IX_Contracts_ExpiryDate ON Contracts(ExpiryDate);

-- =========================================================
-- Assets (tương ứng Bảng 4 "License & Thiết Bị" trên Lark)
-- =========================================================
CREATE TABLE Assets (
    AssetID     INT AUTO_INCREMENT   NOT NULL PRIMARY KEY,
    AssetName   VARCHAR(255)         NOT NULL,        -- Tên Asset
    ContractID  INT                  NOT NULL,
    StartDate   DATE                 NULL,            -- Ngày KH (kích hoạt)
    EndDate     DATE                 NULL,            -- Ngày HH (hết hạn)
    CreatedAt   DATETIME             NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_Assets_Contract FOREIGN KEY (ContractID) REFERENCES Contracts(ContractID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IX_Assets_ContractID ON Assets(ContractID);

-- =========================================================
-- ArchivedTickets
-- Kho lưu trữ lâu dài DUY NHẤT cho ticket Helpdesk đã Closed + Paid.
-- Nhận dữ liệu qua webhook "burn-after-read" từ Tech247 Support (xem ERP_INTEGRATION.md mục 5.1).
-- Support không giữ bản sao sau khi đẩy sang đây thành công.
-- =========================================================
CREATE TABLE ArchivedTickets (
    ArchiveID        INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    TicketNumber     VARCHAR(50)       NOT NULL,        -- ticket_number (vd: TCK-20260814-0003)
    ContractCode     VARCHAR(50)       NOT NULL,        -- contract_id (tham chiếu Contracts.ContractCode)
    RequesterEmail   VARCHAR(255)      NOT NULL,
    IssueSummary     VARCHAR(500)      NOT NULL,
    Description      MEDIUMTEXT        NULL,
    CaseType         VARCHAR(20)       NOT NULL,        -- User | System | Other
    Status           VARCHAR(20)       NOT NULL,        -- Open | In Progress | Resolved | Closed
    BillingStatus    VARCHAR(20)       NOT NULL,        -- Unbilled | Pending_Payment | Paid
    CreatedAtSource  DATETIME          NOT NULL,        -- created_at gốc bên Support
    ResolvedAt       DATETIME          NULL,
    ResolutionNotes  MEDIUMTEXT        NULL,
    LarkRecordID     VARCHAR(100)      NULL,
    ReceivedAt       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_ArchivedTickets_TicketNumber UNIQUE (TicketNumber),
    CONSTRAINT CK_ArchivedTickets_CaseType CHECK (CaseType IN ('User', 'System', 'Other')),
    CONSTRAINT CK_ArchivedTickets_Status CHECK (Status IN ('Open', 'In Progress', 'Resolved', 'Closed')),
    CONSTRAINT CK_ArchivedTickets_BillingStatus CHECK (BillingStatus IN ('Unbilled', 'Pending_Payment', 'Paid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IX_ArchivedTickets_ContractCode ON ArchivedTickets(ContractCode);