--- activity_logs ---
CREATE TABLE `activity_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned DEFAULT NULL,
  `action` varchar(80) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `ip_address` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_logs_user` (`user_id`),
  CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=287 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- customers ---
CREATE TABLE `customers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(160) COLLATE utf8mb4_general_ci NOT NULL,
  `phone` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(190) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `location` varchar(160) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_type` varchar(80) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_customers_user` (`created_by`),
  CONSTRAINT `fk_customers_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- email_verifications ---
CREATE TABLE `email_verifications` (
  `email` varchar(190) COLLATE utf8mb4_general_ci NOT NULL,
  `otp` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `payload` text COLLATE utf8mb4_general_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- login_attempts ---
CREATE TABLE `login_attempts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(190) COLLATE utf8mb4_general_ci NOT NULL,
  `ip_address` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '0',
  `attempted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_login_attempts_email_time` (`email`,`attempted_at`),
  KEY `idx_login_attempts_ip_time` (`ip_address`,`attempted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- password_resets ---
CREATE TABLE `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- products ---
CREATE TABLE `products` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_category` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `product_name` varchar(180) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `brand` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `model` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `unit` varchar(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pcs',
  `unit_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotation_counters ---
CREATE TABLE `quotation_counters` (
  `counter_year` smallint unsigned NOT NULL,
  `next_seq` int unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`counter_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Atomic quotation number sequence per year'
--- quotation_import_items ---
CREATE TABLE `quotation_import_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int unsigned NOT NULL,
  `item_head` varchar(180) COLLATE utf8mb4_general_ci NOT NULL,
  `specification` text COLLATE utf8mb4_general_ci,
  `brand` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `model` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qty` decimal(12,2) NOT NULL DEFAULT '0.00',
  `unit` varchar(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pcs',
  `fob_rate_usd` decimal(12,2) NOT NULL DEFAULT '0.00',
  `fob_total_usd` decimal(12,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_import_items_quotation` (`quotation_id`),
  CONSTRAINT `fk_import_items_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotation_items ---
CREATE TABLE `quotation_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int unsigned NOT NULL,
  `product_id` int unsigned DEFAULT NULL,
  `product_category` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `product_name` varchar(180) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `brand` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `model` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qty` decimal(10,2) NOT NULL DEFAULT '1.00',
  `unit` varchar(40) COLLATE utf8mb4_general_ci NOT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total` decimal(12,2) NOT NULL DEFAULT '0.00',
  `is_custom_item` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_items_quotation` (`quotation_id`),
  KEY `fk_items_product` (`product_id`),
  CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_items_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=482 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotation_loads ---
CREATE TABLE `quotation_loads` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int unsigned NOT NULL,
  `load_name` varchar(160) COLLATE utf8mb4_general_ci NOT NULL,
  `qty` decimal(10,2) NOT NULL DEFAULT '1.00',
  `watt_rating` decimal(10,2) NOT NULL DEFAULT '0.00',
  `backup_when_no_sun` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_watt` decimal(12,2) NOT NULL DEFAULT '0.00',
  `watt_hour` decimal(12,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_loads_quotation` (`quotation_id`),
  CONSTRAINT `fk_loads_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotations ---
CREATE TABLE `quotations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `quotation_no` varchar(40) COLLATE utf8mb4_general_ci NOT NULL,
  `customer_id` int unsigned NOT NULL,
  `system_type` varchar(80) COLLATE utf8mb4_general_ci NOT NULL,
  `calculation_method` varchar(80) COLLATE utf8mb4_general_ci NOT NULL,
  `site_type` varchar(80) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `installation_area` varchar(160) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `panel_mounting` varchar(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Rooftop',
  `monthly_bill` decimal(12,2) NOT NULL DEFAULT '0.00',
  `peak_load_kw` decimal(10,2) NOT NULL DEFAULT '0.00',
  `backup_hours` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pump_hp` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pump_head` decimal(10,2) NOT NULL DEFAULT '0.00',
  `water_demand` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `has_imported_materials` tinyint(1) NOT NULL DEFAULT '0',
  `import_subtotal_usd` decimal(12,2) DEFAULT NULL,
  `lc_fee_percent` decimal(5,2) DEFAULT '7.00',
  `lc_fee_usd` decimal(12,2) DEFAULT NULL,
  `freight_usd` decimal(12,2) DEFAULT NULL,
  `insurance_usd` decimal(12,2) DEFAULT NULL,
  `other_import_cost_usd` decimal(12,2) DEFAULT NULL,
  `total_import_cost_usd` decimal(12,2) DEFAULT NULL,
  `dollar_rate` decimal(10,2) DEFAULT '125.00',
  `total_import_cost_bdt` decimal(14,2) DEFAULT NULL,
  `local_subtotal_excluding_tax` decimal(14,2) DEFAULT NULL,
  `ait_percent` decimal(5,2) DEFAULT '5.00',
  `ait_amount` decimal(14,2) DEFAULT NULL,
  `local_total_including_tax` decimal(14,2) DEFAULT NULL,
  `total_excluding_vat_ait` decimal(14,2) DEFAULT NULL,
  `total_including_vat_ait` decimal(14,2) DEFAULT NULL,
  `margin_percent` decimal(5,2) DEFAULT NULL,
  `margin_amount` decimal(14,2) DEFAULT NULL,
  `final_quotation_amount` decimal(14,2) DEFAULT NULL,
  `dc_capacity_kwp` decimal(10,2) DEFAULT NULL,
  `ac_capacity_kw` decimal(10,2) DEFAULT NULL,
  `cost_per_watt_excluding_tax` decimal(10,2) DEFAULT NULL,
  `cost_per_watt_including_tax` decimal(10,2) DEFAULT NULL,
  `cost_per_kw_excluding_tax` decimal(14,2) DEFAULT NULL,
  `cost_per_kw_including_tax` decimal(14,2) DEFAULT NULL,
  `show_cost_per_watt_on_pdf` tinyint(1) NOT NULL DEFAULT '0',
  `import_tax_note` varchar(255) COLLATE utf8mb4_general_ci DEFAULT 'No VAT & AIT for Import Part',
  `system_capacity_kw` decimal(10,2) DEFAULT NULL,
  `annual_energy_kwh` decimal(12,2) DEFAULT NULL,
  `carbon_reduced_kg` decimal(12,2) DEFAULT NULL,
  `coal_reduced_kg` decimal(12,2) DEFAULT NULL,
  `annual_savings_amount` decimal(12,2) DEFAULT NULL,
  `payback_period_years` decimal(8,2) DEFAULT NULL,
  `average_sun_hour` decimal(5,2) DEFAULT '4.50',
  `performance_ratio` decimal(5,2) DEFAULT '0.75',
  `grid_emission_factor` decimal(5,2) DEFAULT '0.62',
  `coal_factor` decimal(5,2) DEFAULT '0.40',
  `electricity_rate` decimal(8,2) DEFAULT '10.00',
  `battery_voltage` int DEFAULT NULL,
  `battery_ah` int DEFAULT NULL,
  `battery_spec` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pump_efficiency` decimal(5,2) DEFAULT '0.55',
  `system_efficiency` decimal(5,2) DEFAULT '0.75',
  `daily_pumping_energy_kwh` decimal(12,2) DEFAULT NULL,
  `suggested_inverter_kw` decimal(10,2) DEFAULT NULL,
  `pump_type` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'AC',
  `pump_phase` varchar(40) COLLATE utf8mb4_general_ci DEFAULT 'Single Phase',
  `pipe_diameter` varchar(80) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `running_hours_per_day` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT '0.00',
  `vat_percent` decimal(6,2) NOT NULL DEFAULT '0.00',
  `vat_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `grand_total` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','final','sent','accepted','rejected') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `notes` text COLLATE utf8mb4_general_ci,
  `terms_conditions` text COLLATE utf8mb4_general_ci,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotation_no` (`quotation_no`),
  KEY `fk_quotations_customer` (`customer_id`),
  KEY `fk_quotations_user` (`created_by`),
  CONSTRAINT `fk_quotations_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_quotations_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- settings ---
CREATE TABLE `settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(120) COLLATE utf8mb4_general_ci NOT NULL,
  `setting_value` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- users ---
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(190) COLLATE utf8mb4_general_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `role` enum('admin','sales','engineer','viewer','visitor') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'visitor',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `must_change_password` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `mobile_number` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `company_name` varchar(160) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci