-- Trust Tax Advisor - MySQL Database Schema
-- Version 1.0.0
-- cPanel Database: fmpdcrei_tta1

CREATE DATABASE IF NOT EXISTS fmpdcrei_tta1;
USE fmpdcrei_tta1;

-- Users table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  mobile VARCHAR(20) UNIQUE,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  role ENUM('admin', 'staff', 'client', 'connector') DEFAULT 'client',
  is_active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_role (role),
  INDEX idx_mobile (mobile),
  INDEX idx_email (email)
);

-- Connectors table
CREATE TABLE connectors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  commission_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
  commission_value DECIMAL(10, 2) NOT NULL,
  is_active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id)
);

-- Clients table
CREATE TABLE clients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  connector_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (connector_id) REFERENCES connectors(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_connector_id (connector_id)
);

-- Services table
CREATE TABLE services (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  is_active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_name (name)
);

-- Leads table
CREATE TABLE leads (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  mobile VARCHAR(20) NOT NULL,
  email VARCHAR(255),
  service_type VARCHAR(255),
  connector_id INT,
  status ENUM('new', 'contacted', 'interested', 'converted', 'rejected') DEFAULT 'new',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (connector_id) REFERENCES connectors(id) ON DELETE SET NULL,
  INDEX idx_connector_id (connector_id),
  INDEX idx_status (status),
  INDEX idx_mobile (mobile)
);

-- Orders table
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  client_id INT NOT NULL,
  service_id INT,
  connector_id INT,
  amount DECIMAL(12, 2) NOT NULL,
  status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
  payment_status ENUM('pending', 'paid', 'partial', 'failed') DEFAULT 'pending',
  payment_date TIMESTAMP NULL,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL,
  FOREIGN KEY (connector_id) REFERENCES connectors(id) ON DELETE SET NULL,
  INDEX idx_client_id (client_id),
  INDEX idx_connector_id (connector_id),
  INDEX idx_status (status),
  INDEX idx_payment_status (payment_status)
);

-- Commissions table (CORE: Auto-calculated when order is completed)
CREATE TABLE commissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  connector_id INT NOT NULL,
  order_id INT NOT NULL UNIQUE,
  amount DECIMAL(12, 2) NOT NULL,
  status ENUM('pending', 'paid', 'rejected') DEFAULT 'pending',
  paid_date TIMESTAMP NULL,
  payment_method VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (connector_id) REFERENCES connectors(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_connector_id (connector_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

-- Documents table
CREATE TABLE documents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  client_id INT NOT NULL,
  order_id INT,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_type VARCHAR(50),
  file_size BIGINT,
  is_verified BOOLEAN DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_client_id (client_id),
  INDEX idx_order_id (order_id)
);

-- Form Templates table
CREATE TABLE form_templates (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  fields_json LONGTEXT NOT NULL,
  is_active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_name (name)
);

-- Form Responses table
CREATE TABLE form_responses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  form_template_id INT NOT NULL,
  user_id INT NOT NULL,
  response_data LONGTEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (form_template_id) REFERENCES form_templates(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_form_id (form_template_id),
  INDEX idx_user_id (user_id)
);

-- OTP Logs table
CREATE TABLE otp_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  mobile VARCHAR(20),
  email VARCHAR(255),
  otp VARCHAR(10) NOT NULL,
  type ENUM('login', 'verification', 'password_reset') DEFAULT 'login',
  used BOOLEAN DEFAULT 0,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mobile (mobile),
  INDEX idx_email (email),
  INDEX idx_expires_at (expires_at)
);

-- Audit Log table (Optional: for tracking changes)
CREATE TABLE audit_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  action VARCHAR(255) NOT NULL,
  entity_type VARCHAR(100),
  entity_id INT,
  old_values LONGTEXT,
  new_values LONGTEXT,
  ip_address VARCHAR(45),
  user_agent TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
);

-- Create default admin user (password: admin@123)
INSERT INTO users (name, mobile, email, password_hash, role) 
VALUES ('Admin User', '918733870977', 'admin@trusttaxadvisor.com', '$2a$10$YourHashedPasswordHere', 'admin') 
ON DUPLICATE KEY UPDATE id=id;

-- Create indexed views for reporting
CREATE VIEW commission_summary AS
SELECT 
  c.id,
  c.connector_id,
  u.name as connector_name,
  u.mobile as connector_mobile,
  c.order_id,
  c.amount,
  c.status,
  MONTH(c.created_at) as month,
  YEAR(c.created_at) as year,
  c.created_at
FROM commissions c
JOIN connectors cn ON c.connector_id = cn.id
JOIN users u ON cn.user_id = u.id;

-- Commission earning by connector
CREATE VIEW connector_earnings AS
SELECT 
  c.id,
  c.user_id,
  u.name,
  u.mobile,
  u.email,
  c.commission_type,
  c.commission_value,
  COUNT(cm.id) as total_commissions,
  COALESCE(SUM(CASE WHEN cm.status = 'pending' THEN cm.amount ELSE 0 END), 0) as pending_amount,
  COALESCE(SUM(CASE WHEN cm.status = 'paid' THEN cm.amount ELSE 0 END), 0) as paid_amount,
  COALESCE(SUM(cm.amount), 0) as total_earned
FROM connectors c
JOIN users u ON c.user_id = u.id
LEFT JOIN commissions cm ON c.id = cm.connector_id
WHERE c.is_active = 1
GROUP BY c.id;

-- Alter to add import statements if needed
ALTER TABLE orders ADD INDEX idx_created_at (created_at);
ALTER TABLE leads ADD INDEX idx_created_at (created_at);
ALTER TABLE commissions ADD INDEX idx_paid_date (paid_date);