-- sql/schema.sql (v3e)
CREATE TABLE IF NOT EXISTS ts_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  role ENUM('admin','sales','report','system') NOT NULL DEFAULT 'system',
  permissions JSON NULL,
  password_hash VARCHAR(255) NOT NULL,
  can_change_password TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS ts_salespersons ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL );
CREATE TABLE IF NOT EXISTS ts_tally_releases ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL );
CREATE TABLE IF NOT EXISTS ts_datacenters ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL );
CREATE TABLE IF NOT EXISTS ts_servers ( id INT AUTO_INCREMENT PRIMARY KEY, url VARCHAR(200) NOT NULL, ip VARCHAR(50) NOT NULL );
CREATE TABLE IF NOT EXISTS ts_companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(150) NOT NULL UNIQUE,
  customer_name VARCHAR(120) NOT NULL,
  contact_no VARCHAR(20) NOT NULL,
  email VARCHAR(120) NOT NULL,
  tally_release VARCHAR(50) NOT NULL,
  reference_name VARCHAR(120) NULL,
  commission_rs DECIMAL(10,2) DEFAULT 0,
  sales_person VARCHAR(100) NOT NULL,
  price_per_user_rs DECIMAL(10,2) NOT NULL,
  no_of_users INT NOT NULL DEFAULT 1,
  server_type ENUM('Shared','Dedicated','AWS') NOT NULL,
  payment_terms ENUM('Monthly','Quarterly','Half-Yearly','Yearly') NOT NULL
);
CREATE TABLE IF NOT EXISTS ts_company_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  username VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NULL,
  drive CHAR(1) NOT NULL,
  server_id INT NULL,
  datacenter_id INT NULL,
  cloud_type ENUM('VPS','HA','AWS') NULL,
  service_start_date DATE NOT NULL,
  service_expiry_date DATE NOT NULL,
  status ENUM('Active','Disabled') DEFAULT 'Active',
  FOREIGN KEY(company_id) REFERENCES ts_companies(id) ON DELETE CASCADE,
  FOREIGN KEY(server_id) REFERENCES ts_servers(id) ON DELETE SET NULL,
  FOREIGN KEY(datacenter_id) REFERENCES ts_datacenters(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS ts_activity_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  module VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  message VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(user_id) REFERENCES ts_users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS ts_leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  company_name VARCHAR(150) NOT NULL,
  contact_no VARCHAR(20) NOT NULL,
  sales_person VARCHAR(100) NOT NULL,
  status ENUM('Open','Closed') NOT NULL DEFAULT 'Open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  closed_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY(company_id) REFERENCES ts_companies(id) ON DELETE CASCADE
);
