CREATE DATABASE IF NOT EXISTS mobile_repair;
USE mobile_repair;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) DEFAULT '',
  mobile VARCHAR(20) DEFAULT NULL,
  gender VARCHAR(500) DEFAULT '',
  dob VARCHAR(400) DEFAULT '',
  email VARCHAR(255) DEFAULT '',
  address TEXT,
  city VARCHAR(100) DEFAULT '',
  state VARCHAR(100) DEFAULT '',
  accessToken TEXT,
  refreshToken TEXT,
  fcmToken TEXT,
  status VARCHAR(11) DEFAULT '0',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS otp (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(400) DEFAULT NULL,
  otp VARCHAR(250) DEFAULT NULL,
  date_time VARCHAR(250) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS token (
  id INT AUTO_INCREMENT PRIMARY KEY,
  token TEXT,
  refreshToken TEXT,
  fcmToken TEXT,
  mobile VARCHAR(50),
  deviceId VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS brands (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS mobile_models (
  id INT AUTO_INCREMENT PRIMARY KEY,
  brand_id INT NOT NULL,
  model_name VARCHAR(255) NOT NULL,
  model_slug VARCHAR(255) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS mobile_parts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  part_name VARCHAR(255) NOT NULL,
  part_slug VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) DEFAULT 0.00,
  selling_price DECIMAL(10,2) DEFAULT 0.00,
  warranty VARCHAR(100) DEFAULT NULL,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (model_id) REFERENCES mobile_models(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS model_services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  service_name VARCHAR(255) NOT NULL,
  service_slug VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) DEFAULT 0.00,
  guaranty VARCHAR(100) DEFAULT NULL,
  description TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (model_id) REFERENCES mobile_models(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  mobile VARCHAR(20) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  address TEXT,
  city VARCHAR(100) DEFAULT NULL,
  state VARCHAR(100) DEFAULT NULL,
  password VARCHAR(255) NOT NULL,
  salary DECIMAL(10,2) DEFAULT 0.00,
  commission DECIMAL(10,2) DEFAULT 0.00,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) DEFAULT '',
  image VARCHAR(500) NOT NULL,
  type ENUM('main', 'small', 'promo') DEFAULT 'main',
  link VARCHAR(500) DEFAULT '',
  is_active TINYINT(1) DEFAULT 1,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS repairs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  category_id INT DEFAULT NULL,
  brand_id INT DEFAULT NULL,
  model_id INT DEFAULT NULL,
  device_imei VARCHAR(100) DEFAULT NULL,
  issue_description TEXT NOT NULL,
  technician_id INT DEFAULT NULL,
  labor_charges DECIMAL(10,2) DEFAULT 0.00,
  service_charge DECIMAL(10,2) DEFAULT 0.00,
  amount DECIMAL(10,2) DEFAULT 0.00,
  discount DECIMAL(10,2) DEFAULT 0.00,
  total_amount DECIMAL(10,2) DEFAULT 0.00,
  address_id INT DEFAULT NULL,
  customer_mobile VARCHAR(20) DEFAULT NULL,
  commission_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
  commission_value DECIMAL(10,2) DEFAULT 0.00,
  commission_amount DECIMAL(10,2) DEFAULT 0.00,
  commission_paid TINYINT(1) DEFAULT 0,
  status ENUM('pending', 'in_progress', 'completed', 'delivered', 'cancelled') DEFAULT 'pending',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at DATETIME DEFAULT NULL,
  delivered_at DATETIME DEFAULT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (technician_id) REFERENCES employees(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS repair_parts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  repair_id INT NOT NULL,
  part_id INT DEFAULT NULL,
  part_name VARCHAR(255) NOT NULL,
  quantity INT DEFAULT 1,
  price DECIMAL(10,2) DEFAULT 0.00,
  total DECIMAL(10,2) DEFAULT 0.00,
  FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE,
  FOREIGN KEY (part_id) REFERENCES mobile_parts(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS repair_issue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  repair_id INT NOT NULL,
  issue_id VARCHAR(100) DEFAULT NULL,
  issue_name VARCHAR(255) NOT NULL,
  issue_desc TEXT,
  price DECIMAL(10,2) DEFAULT 0.00,
  FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  password_secure VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS commission_payouts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  technician_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  note TEXT,
  payout_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (technician_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS technician_parts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  technician_id INT NOT NULL,
  part_id INT DEFAULT NULL,
  part_name VARCHAR(255) NOT NULL,
  quantity INT DEFAULT 1,
  purchase_price DECIMAL(10,2) DEFAULT 0.00,
  selling_price DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('in_stock', 'sold', 'replaced') DEFAULT 'in_stock',
  repair_id INT DEFAULT NULL,
  notes TEXT,
  added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (technician_id) REFERENCES employees(id) ON DELETE CASCADE,
  FOREIGN KEY (part_id) REFERENCES mobile_parts(id) ON DELETE SET NULL,
  FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS repair_status (
  id INT AUTO_INCREMENT PRIMARY KEY,
  repair_id INT NOT NULL,
  live_status VARCHAR(100) NOT NULL,
  status TINYINT(1) DEFAULT 0,
  date VARCHAR(50) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE
);