--- activity_logs ---
CREATE TABLE `activity_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` varchar(255) 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=87 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- customers ---
CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(160) NOT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `location` varchar(160) DEFAULT NULL,
  `customer_type` varchar(80) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- login_attempts ---
CREATE TABLE `login_attempts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(190) NOT NULL,
  `ip_address` varchar(64) 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=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- password_resets ---
CREATE TABLE `password_resets` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) 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(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_category` varchar(100) NOT NULL,
  `product_name` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `brand` varchar(120) DEFAULT NULL,
  `model` varchar(120) DEFAULT NULL,
  `unit` varchar(40) NOT NULL DEFAULT 'pcs',
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotation_counters ---
CREATE TABLE `quotation_counters` (
  `counter_year` smallint(5) unsigned NOT NULL,
  `next_seq` int(10) 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(10) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int(10) unsigned NOT NULL,
  `item_head` varchar(180) NOT NULL,
  `specification` text DEFAULT NULL,
  `brand` varchar(120) DEFAULT NULL,
  `model` varchar(120) DEFAULT NULL,
  `qty` decimal(12,2) NOT NULL DEFAULT 0.00,
  `unit` varchar(40) 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(10) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `product_category` varchar(100) NOT NULL,
  `product_name` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `brand` varchar(120) DEFAULT NULL,
  `model` varchar(120) DEFAULT NULL,
  `qty` decimal(10,2) NOT NULL DEFAULT 1.00,
  `unit` varchar(40) 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=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotation_loads ---
CREATE TABLE `quotation_loads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int(10) unsigned NOT NULL,
  `load_name` varchar(160) 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=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- quotations ---
CREATE TABLE `quotations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_no` varchar(40) NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `system_type` varchar(80) NOT NULL,
  `calculation_method` varchar(80) NOT NULL,
  `site_type` varchar(80) DEFAULT NULL,
  `installation_area` varchar(160) DEFAULT NULL,
  `panel_mounting` varchar(60) 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) 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) 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(11) DEFAULT NULL,
  `battery_ah` int(11) DEFAULT NULL,
  `battery_spec` varchar(100) 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) DEFAULT 'AC',
  `pump_phase` varchar(40) DEFAULT 'Single Phase',
  `pipe_diameter` varchar(80) 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') NOT NULL DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `terms_conditions` text DEFAULT NULL,
  `created_by` int(10) 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=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- settings ---
CREATE TABLE `settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(120) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
--- users ---
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `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 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci