-- =====================================================================
-- WMS Web  |  COMPLETE database for a FRESH install (one file)
--
-- = schema.sql + migrations 1 to 18 (including 15 indicator protocol, 16 camera capture, 17 dual weighbridge, 18 site sharing), already combined.
-- Use it ONLY on an EMPTY database (phpMyAdmin -> select the empty database -> Import / SQL). Do NOT run it on a database that
-- already has WMS tables, and do not run the migrate_section*.sql files after it - everything is already in here.
--
-- After it has run:
--   1. Create the first admin password (the seeded user "isvadmin" has a placeholder, so nobody can log in yet):
--        make a bcrypt hash of your password (PHP:  echo password_hash('YourPassword', PASSWORD_BCRYPT); )  and run
--        UPDATE tblUser SET password_hash = 'PASTE_THE_HASH_HERE' WHERE username = 'isvadmin';
--   2. Log in as isvadmin, create the Company, Site(s) and Weighbridge(s) under Companies & Sites.
--   3. Optional: Administration -> Site Sharing, to let sites of one company share open jobs / reports.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- TABLES
-- ---------------------------------------------------------------------
CREATE TABLE `tblAgent_Status` (
  `device_id` varchar(50) NOT NULL,
  `applied_version` int NOT NULL DEFAULT 0,
  `config_error` varchar(255) DEFAULT NULL,
  `port_state` varchar(20) DEFAULT NULL,
  `signal_ok` tinyint(1) DEFAULT NULL,
  `agent_version` varchar(40) DEFAULT NULL,
  `last_weight` varchar(20) DEFAULT NULL,
  `raw_tail` text DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  PRIMARY KEY (`device_id`),
  CONSTRAINT `tblAgent_Status_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `tblWeighbridge` (`device_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblAudit_Log` (
  `audit_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `target_type` varchar(50) DEFAULT NULL,
  `target_id` varchar(50) DEFAULT NULL,
  `detail` text DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`audit_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tblAudit_Log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tblUser` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCamera_Capture_Request` (
  `request_id` bigint NOT NULL AUTO_INCREMENT,
  `job_id` bigint NOT NULL,
  `site_id` int NOT NULL,
  `status` enum('Pending','Processing','Done','Failed') DEFAULT 'Pending',
  `fail_reason` varchar(255) DEFAULT NULL,
  `requested_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  `seq_no` int DEFAULT NULL,
  `source` varchar(10) NOT NULL DEFAULT 'auto',
  `device_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`request_id`),
  KEY `job_id` (`job_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `tblCamera_Capture_Request_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tblTransaction_Job` (`job_id`),
  CONSTRAINT `tblCamera_Capture_Request_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCamera_Live` (
  `camera_id` int NOT NULL,
  `requested_until` datetime DEFAULT NULL,
  `frame_at` datetime DEFAULT NULL,
  PRIMARY KEY (`camera_id`),
  CONSTRAINT `tblCamera_Live_ibfk_1` FOREIGN KEY (`camera_id`) REFERENCES `tblCamera_Setting` (`camera_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCamera_Setting` (
  `camera_id` int NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `camera_name` varchar(100) NOT NULL,
  `ip_address` varchar(100) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password_encrypted` varchar(255) DEFAULT NULL,
  `rtsp_path` varchar(255) DEFAULT '/stream1',
  `rtsp_port` int DEFAULT 554,
  `position_label` varchar(50) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `device_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`camera_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `tblCamera_Setting_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCompany` (
  `company_id` int NOT NULL AUTO_INCREMENT,
  `company_name` varchar(150) NOT NULL,
  `status` enum('Active','Suspended') DEFAULT 'Active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `address` text DEFAULT NULL,
  `phone` varchar(40) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `reg_no` varchar(60) DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCompany_Role_Permission` (
  `company_id` int NOT NULL,
  `role_id` int NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `allowed` tinyint(1) NOT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_id`,`role_id`,`permission_key`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `tblCompany_Role_Permission_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `tblCompany_Role_Permission_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tblRole` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblCustomer_Master` (
  `customer_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `customer_name` varchar(150) NOT NULL,
  `contact_no` varchar(30) DEFAULT NULL,
  `payment_terms` enum('Cash','Credit') DEFAULT 'Cash',
  `credit_limit` double DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `uq_customer_name` (`company_id`,`customer_name`),
  CONSTRAINT `tblCustomer_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblDriver_Master` (
  `driver_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `driver_name` varchar(100) NOT NULL,
  `license_no` varchar(50) DEFAULT NULL,
  `contact_no` varchar(30) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`driver_id`),
  UNIQUE KEY `uq_driver_name` (`company_id`,`driver_name`),
  CONSTRAINT `tblDriver_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblField_Setting` (
  `field_setting_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `form_name` varchar(100) NOT NULL,
  `field_key` varchar(100) NOT NULL,
  `display_label` varchar(100) NOT NULL,
  `is_enabled` tinyint(1) DEFAULT 1,
  `sort_order` int DEFAULT 0,
  PRIMARY KEY (`field_setting_id`),
  UNIQUE KEY `company_id` (`company_id`,`form_name`,`field_key`),
  CONSTRAINT `tblField_Setting_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblIndicator_Setting` (
  `device_id` varchar(50) NOT NULL,
  `config_json` text NOT NULL,
  `version` int NOT NULL DEFAULT 1,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`device_id`),
  CONSTRAINT `tblIndicator_Setting_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `tblWeighbridge` (`device_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblLive_Weight` (
  `device_id` varchar(50) NOT NULL,
  `weight` varchar(20) DEFAULT '0',
  `stable` tinyint(1) DEFAULT 0,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`device_id`),
  CONSTRAINT `tblLive_Weight_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `tblWeighbridge` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblPermission` (
  `permission_key` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`permission_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblProduct_Master` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `uom` varchar(20) DEFAULT 'KG',
  `purchase_price` double DEFAULT 0,
  `sales_price` double DEFAULT 0,
  `current_stock` double DEFAULT 0,
  `default_deduction_type` enum('None','Percent','Fixed') DEFAULT 'None',
  `default_deduction_value` double DEFAULT 0,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `uq_product_name` (`company_id`,`product_name`),
  CONSTRAINT `tblProduct_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblReport_Schedule` (
  `schedule_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `frequency` enum('Daily','Weekly','Monthly') NOT NULL,
  `recipient_emails` text NOT NULL,
  `report_type` varchar(50) DEFAULT 'Transaction Summary',
  `last_run_at` datetime DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`schedule_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tblReport_Schedule_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblRole` (
  `role_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `role_name` varchar(30) NOT NULL,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `uq_role_company_name` (`company_id`,`role_name`),
  CONSTRAINT `fk_role_company` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblRole_Permission` (
  `role_id` int NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_key`),
  KEY `permission_key` (`permission_key`),
  CONSTRAINT `tblRole_Permission_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `tblRole` (`role_id`),
  CONSTRAINT `tblRole_Permission_ibfk_2` FOREIGN KEY (`permission_key`) REFERENCES `tblPermission` (`permission_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblSite` (
  `site_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `site_name` varchar(150) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `status` enum('Active','Suspended') DEFAULT 'Active',
  `group_id` int DEFAULT NULL,
  PRIMARY KEY (`site_id`),
  KEY `company_id` (`company_id`),
  KEY `fk_site_group` (`group_id`),
  CONSTRAINT `fk_site_group` FOREIGN KEY (`group_id`) REFERENCES `tblSite_Group` (`group_id`) ON DELETE SET NULL,
  CONSTRAINT `tblSite_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblSite_Group` (
  `group_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `group_name` varchar(100) NOT NULL,
  `share_open_jobs` tinyint(1) NOT NULL DEFAULT 1,
  `share_reports` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `uq_site_group_name` (`company_id`,`group_name`),
  CONSTRAINT `fk_site_group_company` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblStock_Ledger` (
  `ledger_id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `item_id` int NOT NULL,
  `movement_type` enum('In','Out') NOT NULL,
  `quantity` double NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` varchar(50) DEFAULT NULL,
  `balance_after` double NOT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ledger_id`),
  KEY `company_id` (`company_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `tblStock_Ledger_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`),
  CONSTRAINT `tblStock_Ledger_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblProduct_Master` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblSupplier_Master` (
  `supplier_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `supplier_name` varchar(150) NOT NULL,
  `contact_no` varchar(30) DEFAULT NULL,
  `payment_terms` enum('Cash','Credit') DEFAULT 'Cash',
  `credit_limit` double DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`supplier_id`),
  UNIQUE KEY `uq_supplier_name` (`company_id`,`supplier_name`),
  CONSTRAINT `tblSupplier_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTicket_Counter` (
  `site_id` int NOT NULL,
  `period_key` varchar(12) NOT NULL,
  `last_no` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`site_id`,`period_key`),
  CONSTRAINT `tblTicket_Counter_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTicket_Format` (
  `format_id` int NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `template_html` mediumtext DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `paper_size` varchar(20) DEFAULT 'A4',
  PRIMARY KEY (`format_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `tblTicket_Format_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTicket_Numbering` (
  `site_id` int NOT NULL,
  `prefix` varchar(10) NOT NULL DEFAULT '',
  `suffix` varchar(10) NOT NULL DEFAULT '',
  `format` varchar(40) NOT NULL DEFAULT '{SEQ}',
  `seq_digits` int NOT NULL DEFAULT 5,
  `start_no` int NOT NULL DEFAULT 1,
  `reset_cycle` enum('Never','Yearly','Monthly','Daily') NOT NULL DEFAULT 'Monthly',
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`site_id`),
  CONSTRAINT `tblTicket_Numbering_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction` (
  `transaction_id` bigint NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `device_id` varchar(50) NOT NULL,
  `ticket_no` varchar(30) NOT NULL,
  `truck_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `supplier_id` int DEFAULT NULL,
  `gross_weight` double DEFAULT NULL,
  `tare_weight` double DEFAULT NULL,
  `net_weight` double DEFAULT NULL,
  `gross_time` datetime DEFAULT NULL,
  `tare_time` datetime DEFAULT NULL,
  `status` enum('Open','Completed','Void') DEFAULT 'Open',
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  UNIQUE KEY `site_id` (`site_id`,`ticket_no`),
  KEY `device_id` (`device_id`),
  CONSTRAINT `tblTransaction_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`),
  CONSTRAINT `tblTransaction_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `tblWeighbridge` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Item_Line` (
  `line_id` bigint NOT NULL AUTO_INCREMENT,
  `job_id` bigint NOT NULL,
  `item_id` int DEFAULT NULL,
  `from_weighing_id` bigint DEFAULT NULL,
  `to_weighing_id` bigint DEFAULT NULL,
  `gross_weight` double NOT NULL,
  `deduction_type` enum('None','Percent','Fixed') DEFAULT 'None',
  `deduction_value` double DEFAULT 0,
  `net_weight` double NOT NULL,
  `unit_price` double NOT NULL,
  `amount` double NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `item_text` varchar(100) DEFAULT NULL,
  `device_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`line_id`),
  KEY `job_id` (`job_id`),
  KEY `item_id` (`item_id`),
  KEY `from_weighing_id` (`from_weighing_id`),
  KEY `to_weighing_id` (`to_weighing_id`),
  CONSTRAINT `tblTransaction_Item_Line_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tblTransaction_Job` (`job_id`),
  CONSTRAINT `tblTransaction_Item_Line_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblProduct_Master` (`product_id`),
  CONSTRAINT `tblTransaction_Item_Line_ibfk_3` FOREIGN KEY (`from_weighing_id`) REFERENCES `tblTransaction_Weighing` (`weighing_id`),
  CONSTRAINT `tblTransaction_Item_Line_ibfk_4` FOREIGN KEY (`to_weighing_id`) REFERENCES `tblTransaction_Weighing` (`weighing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Job` (
  `job_id` bigint NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `device_id` varchar(50) NOT NULL,
  `ticket_no` varchar(30) NOT NULL,
  `type_id` int DEFAULT NULL,
  `type_auto_detected` tinyint(1) DEFAULT 0,
  `customer_id` int DEFAULT NULL,
  `supplier_id` int DEFAULT NULL,
  `truck_id` int DEFAULT NULL,
  `driver_id` int DEFAULT NULL,
  `transporter_id` int DEFAULT NULL,
  `is_direct_weighing` tinyint(1) DEFAULT 0,
  `status` enum('Open','Completed','Void') DEFAULT 'Open',
  `void_reason` varchar(255) DEFAULT NULL,
  `print_count` int NOT NULL DEFAULT 0,
  `first_printed_at` datetime DEFAULT NULL,
  `public_token` char(32) DEFAULT NULL,
  `gst_percent` decimal(5,2) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `driver_signature_path` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  `truck_text` varchar(100) DEFAULT NULL,
  `driver_text` varchar(100) DEFAULT NULL,
  `transporter_text` varchar(100) DEFAULT NULL,
  `customer_text` varchar(100) DEFAULT NULL,
  `supplier_text` varchar(100) DEFAULT NULL,
  `operator_signature_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `site_id` (`site_id`,`ticket_no`),
  UNIQUE KEY `uq_job_public_token` (`public_token`),
  KEY `device_id` (`device_id`),
  KEY `type_id` (`type_id`),
  CONSTRAINT `tblTransaction_Job_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`),
  CONSTRAINT `tblTransaction_Job_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `tblWeighbridge` (`device_id`),
  CONSTRAINT `tblTransaction_Job_ibfk_3` FOREIGN KEY (`type_id`) REFERENCES `tblTransaction_Type` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Job_Site` (
  `job_id` bigint NOT NULL,
  `site_id` int NOT NULL,
  `first_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`job_id`,`site_id`),
  KEY `idx_job_site_site` (`site_id`),
  CONSTRAINT `fk_job_site_job` FOREIGN KEY (`job_id`) REFERENCES `tblTransaction_Job` (`job_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_job_site_site` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Photo` (
  `photo_id` bigint NOT NULL AUTO_INCREMENT,
  `job_id` bigint NOT NULL,
  `camera_id` int DEFAULT NULL,
  `photo_path` varchar(255) NOT NULL,
  `captured_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `seq_no` int DEFAULT NULL,
  `source` varchar(10) NOT NULL DEFAULT 'auto',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`photo_id`),
  KEY `job_id` (`job_id`),
  KEY `camera_id` (`camera_id`),
  CONSTRAINT `tblTransaction_Photo_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tblTransaction_Job` (`job_id`),
  CONSTRAINT `tblTransaction_Photo_ibfk_2` FOREIGN KEY (`camera_id`) REFERENCES `tblCamera_Setting` (`camera_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Setting` (
  `company_id` int NOT NULL,
  `enforce_weight_sequence` tinyint(1) DEFAULT 1,
  `auto_detect_type` tinyint(1) DEFAULT 1,
  `max_items_per_job` int DEFAULT NULL,
  `vehicle_tare_enabled` tinyint(1) DEFAULT 1,
  `direct_weighing_enabled` tinyint(1) DEFAULT 1,
  `allow_manual_weight` tinyint(1) DEFAULT 0,
  `manual_weight_requires_password` tinyint(1) DEFAULT 1,
  `manual_weight_password_hash` varchar(255) DEFAULT NULL,
  `allow_reprint` tinyint(1) DEFAULT 1,
  `gst_percent` decimal(5,2) DEFAULT 9.00,
  `updated_by` int DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `allow_new_master_entries` tinyint(1) NOT NULL DEFAULT 0,
  `allow_deduction` tinyint(1) NOT NULL DEFAULT 1,
  `allow_pricing` tinyint(1) NOT NULL DEFAULT 1,
  `allow_camera` tinyint(1) NOT NULL DEFAULT 1,
  `allow_edit_transaction` tinyint(1) NOT NULL DEFAULT 1,
  `allow_void_transaction` tinyint(1) NOT NULL DEFAULT 1,
  `allow_email_ticket` tinyint(1) NOT NULL DEFAULT 1,
  `default_deduction_type` varchar(10) NOT NULL DEFAULT 'Fixed',
  `show_tare_on_ticket` tinyint(1) NOT NULL DEFAULT 1,
  `manual_reason_required` tinyint(1) NOT NULL DEFAULT 1,
  `camera_auto_capture` tinyint(1) NOT NULL DEFAULT 1,
  `camera_manual_button` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`company_id`),
  CONSTRAINT `tblTransaction_Setting_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Type` (
  `type_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `type_name` varchar(100) NOT NULL,
  `weight_logic` enum('Increasing','Decreasing','None') DEFAULT 'None',
  `is_purchase_like` tinyint(1) DEFAULT 0,
  `is_sales_like` tinyint(1) DEFAULT 0,
  `sort_order` int DEFAULT 0,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`type_id`),
  UNIQUE KEY `company_id` (`company_id`,`type_name`),
  CONSTRAINT `tblTransaction_Type_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransaction_Weighing` (
  `weighing_id` bigint NOT NULL AUTO_INCREMENT,
  `job_id` bigint NOT NULL,
  `seq_no` int NOT NULL,
  `item_id` int DEFAULT NULL,
  `weight` varchar(20) NOT NULL,
  `is_manual` tinyint(1) DEFAULT 0,
  `manual_reason` varchar(255) DEFAULT NULL,
  `captured_by` int DEFAULT NULL,
  `captured_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `item_text` varchar(100) DEFAULT NULL,
  `device_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`weighing_id`),
  UNIQUE KEY `job_id` (`job_id`,`seq_no`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `tblTransaction_Weighing_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tblTransaction_Job` (`job_id`),
  CONSTRAINT `tblTransaction_Weighing_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblProduct_Master` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTransporter_Master` (
  `transporter_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `transporter_name` varchar(150) NOT NULL,
  `contact_no` varchar(30) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`transporter_id`),
  UNIQUE KEY `uq_transporter_name` (`company_id`,`transporter_name`),
  CONSTRAINT `tblTransporter_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblTruck_Master` (
  `truck_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `truck_no` varchar(30) NOT NULL,
  `driver_name` varchar(100) DEFAULT NULL,
  `tare_weight` double DEFAULT NULL,
  `transporter_id` int DEFAULT NULL,
  `driver_id` int DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`truck_id`),
  UNIQUE KEY `company_id` (`company_id`,`truck_no`),
  KEY `fk_truck_transporter` (`transporter_id`),
  KEY `fk_truck_driver` (`driver_id`),
  CONSTRAINT `fk_truck_driver` FOREIGN KEY (`driver_id`) REFERENCES `tblDriver_Master` (`driver_id`),
  CONSTRAINT `fk_truck_transporter` FOREIGN KEY (`transporter_id`) REFERENCES `tblTransporter_Master` (`transporter_id`),
  CONSTRAINT `tblTruck_Master_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblUser` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `role_id` int NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL,
  `status` enum('Active','Disabled') DEFAULT 'Active',
  `failed_attempts` int DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `company_id` (`company_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `tblUser_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tblCompany` (`company_id`),
  CONSTRAINT `tblUser_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tblRole` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblUser_Site_Access` (
  `user_id` int NOT NULL,
  `site_id` int NOT NULL,
  PRIMARY KEY (`user_id`,`site_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `tblUser_Site_Access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tblUser` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `tblUser_Site_Access_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tblWeighbridge` (
  `device_id` varchar(50) NOT NULL,
  `site_id` int NOT NULL,
  `wb_name` varchar(100) DEFAULT NULL,
  `device_key` varchar(255) NOT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  PRIMARY KEY (`device_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `tblWeighbridge_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `tblSite` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- STARTING ROWS (roles, permissions, and the first admin user)
-- ---------------------------------------------------------------------
INSERT INTO `tblRole` (`role_id`, `company_id`, `role_name`) VALUES (2,NULL,'CompanyAdmin');
INSERT INTO `tblRole` (`role_id`, `company_id`, `role_name`) VALUES (1,NULL,'ISVAdmin');
INSERT INTO `tblRole` (`role_id`, `company_id`, `role_name`) VALUES (4,NULL,'Operator');
INSERT INTO `tblRole` (`role_id`, `company_id`, `role_name`) VALUES (3,NULL,'Supervisor');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('edit_master','Add/edit/delete master data (truck, product, customer...)');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('edit_transaction','Edit a completed weighing transaction (not just void it)');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('export_report','Export reports to Excel/PDF');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('manage_users','Create/edit users within own company');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('view_report','View transaction / weighbridge reports');
INSERT INTO `tblPermission` (`permission_key`, `description`) VALUES ('void_transaction','Void or edit a saved weighing transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'edit_master');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'edit_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'export_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'manage_users');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'view_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (1,'void_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'edit_master');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'edit_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'export_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'manage_users');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'view_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (2,'void_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'edit_master');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'edit_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'export_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'manage_users');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'view_report');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (3,'void_transaction');
INSERT INTO `tblRole_Permission` (`role_id`, `permission_key`) VALUES (4,'view_report');
INSERT INTO `tblUser` (`user_id`, `company_id`, `role_id`, `username`, `password_hash`, `full_name`, `signature_path`, `status`, `failed_attempts`, `locked_until`, `created_at`) VALUES (1,NULL,1,'isvadmin','$2y$10$REPLACE.WITH.REAL.BCRYPT.HASH.OUTPUT..........','ISVASIA Admin',NULL,'Active',0,NULL,'2026-11-10 20:29:10');

SET FOREIGN_KEY_CHECKS = 1;
-- end of file