-- SQL schema for License Manager (MySQL/MariaDB)
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS Sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  domain VARCHAR(255),
  token VARCHAR(255),
  status TINYINT(1) DEFAULT 1,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  token VARCHAR(255),
  status TINYINT(1) DEFAULT 1,
  siteId INT NOT NULL,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(siteId) REFERENCES Sites(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  phone VARCHAR(50),
  firstName VARCHAR(255),
  lastName VARCHAR(255),
  siteId INT,
  productId INT,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(siteId) REFERENCES Sites(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Licenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT,
  productId INT,
  active TINYINT(1) DEFAULT 1,
  expiresAt DATETIME,
  issuanceType VARCHAR(50) DEFAULT 'initial',
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(userId) REFERENCES Users(id) ON DELETE CASCADE,
  FOREIGN KEY(productId) REFERENCES Products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  licenseId INT,
  kind VARCHAR(50) NOT NULL,
  accountNumber VARCHAR(255) NOT NULL,
  brokerName VARCHAR(255),
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(licenseId) REFERENCES Licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Trades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT,
  ticket INT,
  symbol VARCHAR(50),
  type VARCHAR(50),
  volume REAL,
  openTime DATETIME,
  closeTime DATETIME,
  profit REAL DEFAULT 0,
  comment TEXT,
  accountNumber VARCHAR(255),
  rawData TEXT,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(userId) REFERENCES Users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX IF NOT EXISTS idx_trades_user ON Trades(userId);
CREATE INDEX IF NOT EXISTS idx_trades_account ON Trades(accountNumber);

CREATE TABLE IF NOT EXISTS Admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255) NOT NULL UNIQUE,
  passwordHash VARCHAR(255) NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  permissions JSON NOT NULL,
  isSuper TINYINT(1) DEFAULT 1,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_products_siteId ON Products(siteId);
CREATE INDEX IF NOT EXISTS idx_users_site_product ON Users(siteId, productId);
CREATE INDEX IF NOT EXISTS idx_licenses_product_issue ON Licenses(productId, issuanceType);
CREATE INDEX IF NOT EXISTS idx_accounts_number ON Accounts(accountNumber);