CREATE DATABASE IF NOT EXISTS fortunes_quotation CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fortunes_quotation;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','sales','engineer','viewer') NOT NULL DEFAULT 'viewer',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(160) NOT NULL,
  phone VARCHAR(60) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  location VARCHAR(160) NULL,
  customer_type VARCHAR(80) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_category VARCHAR(100) NOT NULL,
  product_name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  unit VARCHAR(40) NOT NULL DEFAULT 'pcs',
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE quotations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quotation_no VARCHAR(40) NOT NULL UNIQUE,
  customer_id INT UNSIGNED NOT NULL,
  system_type VARCHAR(80) NOT NULL,
  calculation_method VARCHAR(80) NOT NULL,
  site_type VARCHAR(80) NULL,
  installation_area VARCHAR(160) NULL,
  panel_mounting VARCHAR(60) NOT NULL DEFAULT 'Rooftop',
  monthly_bill DECIMAL(12,2) NOT NULL DEFAULT 0,
  peak_load_kw DECIMAL(10,2) NOT NULL DEFAULT 0,
  backup_hours DECIMAL(10,2) NOT NULL DEFAULT 0,
  pump_hp DECIMAL(10,2) NOT NULL DEFAULT 0,
  pump_head DECIMAL(10,2) NOT NULL DEFAULT 0,
  water_demand VARCHAR(120) NULL,
  has_imported_materials TINYINT(1) NOT NULL DEFAULT 0,
  import_subtotal_usd DECIMAL(12,2) NULL,
  lc_fee_percent DECIMAL(5,2) NULL DEFAULT 7,
  lc_fee_usd DECIMAL(12,2) NULL,
  freight_usd DECIMAL(12,2) NULL,
  insurance_usd DECIMAL(12,2) NULL,
  other_import_cost_usd DECIMAL(12,2) NULL,
  total_import_cost_usd DECIMAL(12,2) NULL,
  dollar_rate DECIMAL(10,2) NULL DEFAULT 125,
  total_import_cost_bdt DECIMAL(14,2) NULL,
  local_subtotal_excluding_tax DECIMAL(14,2) NULL,
  ait_percent DECIMAL(5,2) NULL DEFAULT 5,
  ait_amount DECIMAL(14,2) NULL,
  local_total_including_tax DECIMAL(14,2) NULL,
  total_excluding_vat_ait DECIMAL(14,2) NULL,
  total_including_vat_ait DECIMAL(14,2) NULL,
  margin_percent DECIMAL(5,2) NULL,
  margin_amount DECIMAL(14,2) NULL,
  final_quotation_amount DECIMAL(14,2) NULL,
  dc_capacity_kwp DECIMAL(10,2) NULL,
  ac_capacity_kw DECIMAL(10,2) NULL,
  cost_per_watt_excluding_tax DECIMAL(10,2) NULL,
  cost_per_watt_including_tax DECIMAL(10,2) NULL,
  cost_per_kw_excluding_tax DECIMAL(14,2) NULL,
  cost_per_kw_including_tax DECIMAL(14,2) NULL,
  show_cost_per_watt_on_pdf TINYINT(1) NOT NULL DEFAULT 0,
  import_tax_note VARCHAR(255) NULL DEFAULT 'No VAT & AIT for Import Part',
  system_capacity_kw DECIMAL(10,2) NULL,
  annual_energy_kwh DECIMAL(12,2) NULL,
  carbon_reduced_kg DECIMAL(12,2) NULL,
  coal_reduced_kg DECIMAL(12,2) NULL,
  annual_savings_amount DECIMAL(12,2) NULL,
  payback_period_years DECIMAL(8,2) NULL,
  average_sun_hour DECIMAL(5,2) DEFAULT 4.5,
  performance_ratio DECIMAL(5,2) DEFAULT 0.75,
  grid_emission_factor DECIMAL(5,2) DEFAULT 0.62,
  coal_factor DECIMAL(5,2) DEFAULT 0.4,
  electricity_rate DECIMAL(8,2) DEFAULT 10,
  battery_voltage INT NULL,
  battery_ah INT NULL,
  battery_spec VARCHAR(100) NULL,
  pump_efficiency DECIMAL(5,2) DEFAULT 0.55,
  system_efficiency DECIMAL(5,2) DEFAULT 0.75,
  daily_pumping_energy_kwh DECIMAL(12,2) NULL,
  suggested_inverter_kw DECIMAL(10,2) NULL,
  pump_type VARCHAR(20) DEFAULT 'AC',
  pump_phase VARCHAR(40) DEFAULT 'Single Phase',
  pipe_diameter VARCHAR(80) NULL,
  running_hours_per_day DECIMAL(10,2) NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat_percent DECIMAL(6,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('draft','final','sent','accepted','rejected') NOT NULL DEFAULT 'draft',
  notes TEXT NULL,
  terms_conditions TEXT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_quotations_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_quotations_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE quotation_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NULL,
  product_category VARCHAR(100) NOT NULL,
  product_name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  qty DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit VARCHAR(40) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_custom_item TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_items_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE quotation_loads (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT UNSIGNED NOT NULL,
  load_name VARCHAR(160) NOT NULL,
  qty DECIMAL(10,2) NOT NULL DEFAULT 1,
  watt_rating DECIMAL(10,2) NOT NULL DEFAULT 0,
  backup_when_no_sun DECIMAL(10,2) NOT NULL DEFAULT 0,
  total_watt DECIMAL(12,2) NOT NULL DEFAULT 0,
  watt_hour DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_loads_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE quotation_import_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT UNSIGNED NOT NULL,
  item_head VARCHAR(180) NOT NULL,
  specification TEXT NULL,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit VARCHAR(40) NOT NULL DEFAULT 'pcs',
  fob_rate_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
  fob_total_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_import_items_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  description TEXT NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_attempts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  ip_address VARCHAR(64) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL,
  INDEX idx_login_attempts_email_time (email, attempted_at),
  INDEX idx_login_attempts_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
