-- License Manager database import file
-- Import this file in phpMyAdmin or via MySQL CLI.
-- Default admin login:
--   Email: admin@example.com
--   Password: admin123

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS Trades;
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS Licenses;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Sites;
DROP TABLE IF EXISTS Admins;

CREATE TABLE Sites (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  domain VARCHAR(255) NULL,
  token VARCHAR(255) NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sites_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  token VARCHAR(255) NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  siteId INT UNSIGNED NOT NULL,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_products_token (token),
  KEY idx_products_siteId (siteId),
  CONSTRAINT fk_products_site FOREIGN KEY (siteId) REFERENCES Sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NULL,
  phone VARCHAR(50) NULL,
  firstName VARCHAR(255) NULL,
  lastName VARCHAR(255) NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  siteId INT UNSIGNED NULL,
  productId INT UNSIGNED NULL,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_users_site_product (siteId, productId),
  CONSTRAINT fk_users_site FOREIGN KEY (siteId) REFERENCES Sites(id) ON DELETE SET NULL,
  CONSTRAINT fk_users_product FOREIGN KEY (productId) REFERENCES Products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Licenses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId INT UNSIGNED NULL,
  productId INT UNSIGNED NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  expiresAt DATETIME NULL,
  issuanceType VARCHAR(50) NOT NULL DEFAULT 'initial',
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_licenses_user (userId),
  KEY idx_licenses_product_issue (productId, issuanceType),
  CONSTRAINT fk_licenses_user FOREIGN KEY (userId) REFERENCES Users(id) ON DELETE CASCADE,
  CONSTRAINT fk_licenses_product FOREIGN KEY (productId) REFERENCES Products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  licenseId INT UNSIGNED NULL,
  kind VARCHAR(50) NOT NULL,
  accountNumber VARCHAR(255) NOT NULL,
  brokerName VARCHAR(255) NULL,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_accounts_license (licenseId),
  KEY idx_accounts_number (accountNumber),
  CONSTRAINT fk_accounts_license FOREIGN KEY (licenseId) REFERENCES Licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Trades (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId INT UNSIGNED NULL,
  ticket INT NULL,
  symbol VARCHAR(50) NULL,
  type VARCHAR(50) NULL,
  volume DOUBLE NULL,
  openTime DATETIME NULL,
  closeTime DATETIME NULL,
  profit DOUBLE NOT NULL DEFAULT 0,
  comment TEXT NULL,
  accountNumber VARCHAR(255) NULL,
  rawData JSON NULL,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_trades_user (userId),
  KEY idx_trades_account (accountNumber),
  CONSTRAINT fk_trades_user FOREIGN KEY (userId) REFERENCES Users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NULL,
  email VARCHAR(255) NOT NULL,
  passwordHash VARCHAR(255) NOT NULL,
  token VARCHAR(255) NOT NULL,
  permissions JSON NOT NULL,
  isSuper TINYINT(1) NOT NULL DEFAULT 1,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admins_email (email),
  UNIQUE KEY uq_admins_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO Admins (name, email, passwordHash, token, permissions, isSuper)
VALUES (
  'Main Admin',
  'admin@example.com',
  SHA2(CONCAT('license-manager', ':', 'admin123'), 256),
  REPLACE(UUID(), '-', ''),
  JSON_ARRAY('overview', 'users', 'sites', 'products', 'licenses', 'trades', 'reports', 'admins'),
  1
);

SET FOREIGN_KEY_CHECKS = 1;