213 lines
8.1 KiB
SQL
213 lines
8.1 KiB
SQL
-- 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);
|