
CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  duration_days INT NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  broker VARCHAR(190) DEFAULT NULL,
  account_number VARCHAR(64) NOT NULL,
  role ENUM('MASTER','SLAVE') NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  settings JSON DEFAULT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_acct (user_id, account_number, role)
);
CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  account_id INT NOT NULL,
  plan_id INT NOT NULL,
  starts_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  lot_multiplier DECIMAL(8,2) NOT NULL DEFAULT 1.00,
  symbol_map JSON DEFAULT NULL,
  simulation TINYINT(1) NOT NULL DEFAULT 0,
  simulate_manual TINYINT(1) NOT NULL DEFAULT 0,
  manual_comment VARCHAR(64) DEFAULT 'Mobile',
  max_daily_loss DECIMAL(16,4) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS signals (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  master_account_id INT NOT NULL,
  event ENUM('open','close','modify') NOT NULL,
  symbol VARCHAR(20) NOT NULL,
  side ENUM('BUY','SELL') NOT NULL,
  lot DECIMAL(10,2) NOT NULL,
  price DECIMAL(16,5) DEFAULT NULL,
  sl DECIMAL(16,5) DEFAULT NULL,
  tp DECIMAL(16,5) DEFAULT NULL,
  ticket VARCHAR(32) DEFAULT NULL,
  magic INT DEFAULT NULL,
  status ENUM('OPEN','CLOSED','MODIFIED') NOT NULL DEFAULT 'OPEN',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS executions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  signal_id BIGINT NOT NULL,
  slave_account_id INT NOT NULL,
  status ENUM('PENDING','FILLED','REJECTED') DEFAULT 'PENDING',
  detail VARCHAR(255) DEFAULT NULL,
  pl DECIMAL(16,4) DEFAULT NULL,
  execution_source VARCHAR(32) DEFAULT 'EA',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO plans (name, duration_days, price, is_active) VALUES
('Mensual', 30, 29.00, 1)
ON DUPLICATE KEY UPDATE name=name;
