-- Database schema for license system

CREATE TABLE IF NOT EXISTS Projects (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32) UNIQUE NOT NULL,
    ProjectKey      VARCHAR(64) NOT NULL,
    ProjectSecret   VARCHAR(64) NOT NULL,
    Name            VARCHAR(100) NOT NULL,
    Description     TEXT,
    IconUrl         VARCHAR(500),
    MaxDevices      INT DEFAULT 1,
    AutoUpdate      BOOLEAN DEFAULT TRUE,
    IsEnabled       BOOLEAN DEFAULT TRUE,
    DocsContent     TEXT,
    CreatedBy       INT,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ProjectPricing (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32) REFERENCES Projects(ProjectId) ON DELETE CASCADE,
    CardType        VARCHAR(20) NOT NULL,
    DurationDays    INT NOT NULL,
    OriginalPrice   DECIMAL(10,2) NOT NULL,
    IsEnabled       BOOLEAN DEFAULT TRUE,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(ProjectId, CardType, DurationDays)
);

CREATE TABLE IF NOT EXISTS SoftwareVersions (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32) REFERENCES Projects(ProjectId) ON DELETE CASCADE,
    Version         VARCHAR(20) NOT NULL,
    FileUrl         VARCHAR(500) NOT NULL,
    FileSize        BIGINT,
    FileHash        VARCHAR(64),
    EncryptionKey   VARCHAR(256),
    Changelog       TEXT,
    IsForceUpdate   BOOLEAN DEFAULT FALSE,
    IsStable        BOOLEAN DEFAULT TRUE,
    PublishedAt     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CreatedBy       INT,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(ProjectId, Version)
);

CREATE TABLE IF NOT EXISTS Admins (
    Id              SERIAL PRIMARY KEY,
    Username        VARCHAR(50) UNIQUE NOT NULL,
    PasswordHash    VARCHAR(255) NOT NULL,
    Email           VARCHAR(100),
    Role            VARCHAR(20) DEFAULT 'admin',
    Permissions     TEXT,
    Status          VARCHAR(20) DEFAULT 'active',
    LastLoginAt     TIMESTAMP,
    LastLoginIp     VARCHAR(45),
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Agents (
    Id              SERIAL PRIMARY KEY,
    AdminId         INT REFERENCES Admins(Id) ON DELETE SET NULL,
    AgentCode       VARCHAR(20) UNIQUE NOT NULL,
    CompanyName     VARCHAR(100),
    ContactPerson   VARCHAR(50),
    ContactPhone    VARCHAR(20),
    ContactEmail    VARCHAR(100),
    PasswordHash    VARCHAR(255) NOT NULL,
    Balance         DECIMAL(10,2) DEFAULT 0,
    Discount        DECIMAL(5,2) DEFAULT 100.00,
    CreditLimit     DECIMAL(10,2) DEFAULT 0,
    MaxProjects     INT DEFAULT 0,
    AllowedProjects TEXT,
    Status          VARCHAR(20) DEFAULT 'active',
    LastLoginAt     TIMESTAMP,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS CardKeys (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32) REFERENCES Projects(ProjectId) ON DELETE SET NULL,
    KeyCode         VARCHAR(32) UNIQUE NOT NULL,
    CardType        VARCHAR(20) NOT NULL,
    DurationDays    INT NOT NULL,
    ExpireTime      TIMESTAMP,
    MaxDevices      INT DEFAULT 1,
    MachineCode     VARCHAR(64),
    Status          VARCHAR(20) DEFAULT 'unused',
    ActivateTime    TIMESTAMP,
    LastUsedAt      TIMESTAMP,
    UsedDuration    BIGINT DEFAULT 0,
    GeneratedBy     INT REFERENCES Admins(Id),
    AgentId         INT REFERENCES Agents(Id),
    SoldPrice       DECIMAL(10,2),
    Note            VARCHAR(200),
    BatchId         VARCHAR(36),
    Version         INT DEFAULT 1,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    DeletedAt       TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_keys_project ON CardKeys(ProjectId);
CREATE INDEX IF NOT EXISTS idx_card_keys_code ON CardKeys(KeyCode);
CREATE INDEX IF NOT EXISTS idx_card_keys_status ON CardKeys(Status);
CREATE INDEX IF NOT EXISTS idx_card_keys_project_status_created ON CardKeys(ProjectId, Status, CreatedAt DESC) WHERE DeletedAt IS NULL;
CREATE INDEX IF NOT EXISTS idx_card_keys_expire ON CardKeys(ExpireTime) WHERE Status = 'active' AND DeletedAt IS NULL;
CREATE INDEX IF NOT EXISTS idx_card_keys_agent ON CardKeys(AgentId) WHERE DeletedAt IS NULL;
CREATE INDEX IF NOT EXISTS idx_card_keys_batch ON CardKeys(BatchId);

CREATE TABLE IF NOT EXISTS Devices (
    Id              SERIAL PRIMARY KEY,
    CardKeyId       INT REFERENCES CardKeys(Id) ON DELETE CASCADE,
    DeviceId        VARCHAR(64) NOT NULL,
    DeviceName      VARCHAR(100),
    OsInfo          VARCHAR(100),
    LastHeartbeat   TIMESTAMP,
    IpAddress       VARCHAR(45),
    Location        VARCHAR(100),
    IsActive        BOOLEAN DEFAULT TRUE,
    FirstLoginAt    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    DeletedAt       TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_devices_card_key ON Devices(CardKeyId);
CREATE INDEX IF NOT EXISTS idx_devices_device_id ON Devices(DeviceId);
CREATE UNIQUE INDEX IF NOT EXISTS idx_devices_card_key_device ON Devices(CardKeyId, DeviceId);
CREATE INDEX IF NOT EXISTS idx_devices_heartbeat ON Devices(LastHeartbeat) WHERE IsActive = true AND DeletedAt IS NULL;

CREATE TABLE IF NOT EXISTS AccessLogs (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32),
    CardKeyId       INT,
    DeviceId        VARCHAR(64),
    Action          VARCHAR(50),
    IpAddress       INET,
    UserAgent       TEXT,
    ResponseCode    INT,
    ResponseTime    INT,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_logs_project ON AccessLogs(ProjectId);
CREATE INDEX IF NOT EXISTS idx_logs_created ON AccessLogs(CreatedAt);
CREATE INDEX IF NOT EXISTS idx_logs_action_created ON AccessLogs(Action, CreatedAt DESC);
CREATE INDEX IF NOT EXISTS idx_logs_card_key ON AccessLogs(CardKeyId);

CREATE TABLE IF NOT EXISTS Statistics (
    Id              SERIAL PRIMARY KEY,
    ProjectId       VARCHAR(32),
    Date            DATE,
    ActiveUsers     INT DEFAULT 0,
    NewUsers        INT DEFAULT 0,
    TotalDownloads  INT DEFAULT 0,
    TotalDuration   BIGINT DEFAULT 0,
    Revenue         DECIMAL(10,2) DEFAULT 0,
    UNIQUE(ProjectId, Date)
);

CREATE TABLE IF NOT EXISTS AgentTransactions (
    Id              SERIAL PRIMARY KEY,
    AgentId         INT REFERENCES Agents(Id) ON DELETE CASCADE,
    Type            VARCHAR(20) NOT NULL,
    Amount          DECIMAL(10,2) NOT NULL,
    BalanceBefore   DECIMAL(10,2) NOT NULL,
    BalanceAfter    DECIMAL(10,2) NOT NULL,
    CardKeyId       INT REFERENCES CardKeys(Id) ON DELETE SET NULL,
    Remark          VARCHAR(200),
    CreatedBy       INT REFERENCES Admins(Id) ON DELETE SET NULL,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS CardKeyLogs (
    Id              SERIAL PRIMARY KEY,
    CardKeyId       INT REFERENCES CardKeys(Id) ON DELETE CASCADE,
    Action          VARCHAR(50) NOT NULL,
    OperatorId      INT,
    OperatorType    VARCHAR(20),
    Details         TEXT,
    IpAddress       VARCHAR(45),
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS SystemConfigs (
    Id              SERIAL PRIMARY KEY,
    ConfigKey       VARCHAR(50) UNIQUE NOT NULL,
    ConfigValue     TEXT,
    ValueType       VARCHAR(20) DEFAULT 'string',
    Category        VARCHAR(50) DEFAULT 'general',
    DisplayName     VARCHAR(100),
    Description     VARCHAR(500),
    Options         TEXT,
    IsPublic        BOOLEAN DEFAULT FALSE,
    UpdatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS IdempotencyKeys (
    Id              SERIAL PRIMARY KEY,
    IdempotencyKey  VARCHAR(64) UNIQUE NOT NULL,
    RequestPath     VARCHAR(200) NOT NULL,
    RequestHash     VARCHAR(64),
    ResponseCode    INT,
    ResponseBody    TEXT,
    CreatedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ExpiresAt       TIMESTAMP NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_idempotency_key ON IdempotencyKeys(IdempotencyKey);
CREATE INDEX IF NOT EXISTS idx_idempotency_expires ON IdempotencyKeys(ExpiresAt);
