-- =====================================================================
-- SITEFLOW: Construction & Plumbing Business Management Database Schema
-- Compatible with MySQL 8.0+ and MariaDB 10.4+
-- Charset: utf8mb4, Collation: utf8mb4_unicode_ci
-- =====================================================================

CREATE DATABASE IF NOT EXISTS `siteflow` 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE `siteflow`;

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. Table: users
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `phone` VARCHAR(20) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `pin_hash` VARCHAR(255) DEFAULT NULL,
  `role` ENUM('owner', 'manager') NOT NULL DEFAULT 'owner',
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `auth_token` VARCHAR(255) DEFAULT NULL,
  `token_expires_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_users_uuid` (`uuid`),
  KEY `idx_users_email` (`email`),
  KEY `idx_users_auth_token` (`auth_token`),
  KEY `idx_users_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 2. Table: business_settings
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `business_settings`;
CREATE TABLE `business_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `business_name` VARCHAR(200) NOT NULL DEFAULT 'SiteFlow Construction & Plumbing',
  `owner_name` VARCHAR(150) NOT NULL DEFAULT 'Business Owner',
  `tagline` VARCHAR(255) DEFAULT 'Every Site. Every Expense. Every Payment.',
  `contact_number_1` VARCHAR(20) NOT NULL DEFAULT '+91 98765 43210',
  `contact_number_2` VARCHAR(20) DEFAULT NULL,
  `whatsapp_number` VARCHAR(20) DEFAULT '+91 98765 43210',
  `email` VARCHAR(150) DEFAULT 'info@siteflow.webnovatech.co.in',
  `address_line1` VARCHAR(255) DEFAULT 'Shop No. 12, Contractor Plaza',
  `address_line2` VARCHAR(255) DEFAULT 'Near City Circle, Main Road',
  `city` VARCHAR(100) DEFAULT 'Ahmedabad',
  `state` VARCHAR(100) DEFAULT 'Gujarat',
  `pincode` VARCHAR(10) DEFAULT '380001',
  `gstin` VARCHAR(20) DEFAULT NULL,
  `pan_number` VARCHAR(20) DEFAULT NULL,
  `bank_name` VARCHAR(150) DEFAULT 'State Bank of India',
  `bank_account_no` VARCHAR(50) DEFAULT '123456789012',
  `bank_ifsc` VARCHAR(20) DEFAULT 'SBIN0001234',
  `bank_branch` VARCHAR(100) DEFAULT 'Main Branch',
  `upi_id` VARCHAR(100) DEFAULT 'siteflow@upi',
  `upi_qr_path` VARCHAR(255) DEFAULT NULL,
  `logo_path` VARCHAR(255) DEFAULT NULL,
  `signature_path` VARCHAR(255) DEFAULT NULL,
  `invoice_prefix` VARCHAR(10) NOT NULL DEFAULT 'SF-',
  `invoice_next_number` INT UNSIGNED NOT NULL DEFAULT 1001,
  `terms_and_conditions` TEXT DEFAULT '1. Payment is due within 7 days of invoice date.\n2. In case of delay, 1.5% interest per month will be applicable.\n3. All disputes are subject to local jurisdiction.',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3. Table: clients
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `company_name` VARCHAR(200) DEFAULT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `whatsapp_number` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `billing_address` TEXT DEFAULT NULL,
  `gstin` VARCHAR(20) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_clients_uuid` (`uuid`),
  KEY `idx_clients_phone` (`phone`),
  KEY `idx_clients_name` (`name`),
  KEY `idx_clients_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. Table: projects
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `project_code` VARCHAR(50) NOT NULL,
  `project_name` VARCHAR(200) NOT NULL,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `client_name_cached` VARCHAR(150) DEFAULT NULL,
  `client_phone_cached` VARCHAR(20) DEFAULT NULL,
  `project_address` TEXT DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `start_date` DATE NOT NULL,
  `expected_completion_date` DATE DEFAULT NULL,
  `actual_completion_date` DATE DEFAULT NULL,
  `agreed_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `advance_received` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('Upcoming', 'Active', 'On Hold', 'Completed', 'Cancelled') NOT NULL DEFAULT 'Upcoming',
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_projects_uuid` (`uuid`),
  UNIQUE KEY `idx_projects_code` (`project_code`),
  KEY `idx_projects_client_id` (`client_id`),
  KEY `idx_projects_status` (`status`),
  KEY `idx_projects_start_date` (`start_date`),
  KEY `idx_projects_deleted` (`deleted_at`),
  CONSTRAINT `fk_projects_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 5. Table: expense_categories
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `expense_categories`;
CREATE TABLE `expense_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `description` VARCHAR(255) DEFAULT NULL,
  `icon_name` VARCHAR(50) DEFAULT 'Category',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6. Table: expenses
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `expense_code` VARCHAR(50) NOT NULL,
  `expense_date` DATE NOT NULL,
  `project_id` BIGINT UNSIGNED DEFAULT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `description` TEXT NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` ENUM('Cash', 'UPI', 'Bank Transfer', 'Cheque', 'Other') NOT NULL DEFAULT 'Cash',
  `paid_to_vendor` VARCHAR(150) DEFAULT NULL,
  `receipt_image_path` VARCHAR(255) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_expenses_uuid` (`uuid`),
  KEY `idx_expenses_code` (`expense_code`),
  KEY `idx_expenses_date` (`expense_date`),
  KEY `idx_expenses_project_id` (`project_id`),
  KEY `idx_expenses_category_id` (`category_id`),
  KEY `idx_expenses_deleted` (`deleted_at`),
  CONSTRAINT `fk_expenses_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_expenses_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_expenses_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7. Table: payments (Payment Received from Client)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `payment_code` VARCHAR(50) NOT NULL,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `project_id` BIGINT UNSIGNED DEFAULT NULL,
  `payment_date` DATE NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` ENUM('Cash', 'UPI', 'Bank Transfer', 'Cheque', 'Other') NOT NULL DEFAULT 'Cash',
  `transaction_reference` VARCHAR(100) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `receipt_pdf_path` VARCHAR(255) DEFAULT NULL,
  `attachment_path` VARCHAR(255) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_payments_uuid` (`uuid`),
  KEY `idx_payments_code` (`payment_code`),
  KEY `idx_payments_date` (`payment_date`),
  KEY `idx_payments_client_id` (`client_id`),
  KEY `idx_payments_project_id` (`project_id`),
  KEY `idx_payments_deleted` (`deleted_at`),
  CONSTRAINT `fk_payments_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 8. Table: invoices
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
  `invoice_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `project_id` BIGINT UNSIGNED DEFAULT NULL,
  `work_description` TEXT DEFAULT NULL,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `grand_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `previous_advance_adjusted` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `balance_due` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('Draft', 'Sent', 'Partially Paid', 'Paid', 'Overdue', 'Cancelled') NOT NULL DEFAULT 'Draft',
  `payment_terms` TEXT DEFAULT NULL,
  `additional_notes` TEXT DEFAULT NULL,
  `pdf_file_path` VARCHAR(255) DEFAULT NULL,
  `created_by` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_invoices_uuid` (`uuid`),
  UNIQUE KEY `idx_invoices_number` (`invoice_number`),
  KEY `idx_invoices_date` (`invoice_date`),
  KEY `idx_invoices_client_id` (`client_id`),
  KEY `idx_invoices_project_id` (`project_id`),
  KEY `idx_invoices_status` (`status`),
  KEY `idx_invoices_deleted` (`deleted_at`),
  CONSTRAINT `fk_invoices_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_invoices_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_invoices_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 9. Table: invoice_items
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `item_description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit` VARCHAR(50) NOT NULL DEFAULT 'Nos',
  `rate` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_items_invoice_id` (`invoice_id`),
  CONSTRAINT `fk_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 10. Table: invoice_payments
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `invoice_payments`;
CREATE TABLE `invoice_payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `amount_allocated` DECIMAL(12,2) NOT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inv_pay_invoice` (`invoice_id`),
  KEY `idx_inv_pay_payment` (`payment_id`),
  CONSTRAINT `fk_inv_pay_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_inv_pay_payment` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 11. Table: materials
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `materials`;
CREATE TABLE `materials` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `material_name` VARCHAR(150) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL,
  `unit` VARCHAR(50) NOT NULL,
  `purchase_price` DECIMAL(12,2) NOT NULL,
  `vendor_name` VARCHAR(150) DEFAULT NULL,
  `project_id` BIGINT UNSIGNED DEFAULT NULL,
  `purchase_date` DATE NOT NULL,
  `expense_id` BIGINT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_materials_uuid` (`uuid`),
  KEY `idx_materials_project_id` (`project_id`),
  KEY `idx_materials_expense_id` (`expense_id`),
  KEY `idx_materials_deleted` (`deleted_at`),
  CONSTRAINT `fk_materials_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_materials_expense` FOREIGN KEY (`expense_id`) REFERENCES `expenses` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 12. Table: labour_records
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `labour_records`;
CREATE TABLE `labour_records` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `worker_name` VARCHAR(150) NOT NULL,
  `work_description` VARCHAR(255) DEFAULT NULL,
  `project_id` BIGINT UNSIGNED DEFAULT NULL,
  `daily_wage_or_amount` DECIMAL(12,2) NOT NULL,
  `work_date` DATE NOT NULL,
  `payment_date` DATE DEFAULT NULL,
  `is_paid` TINYINT(1) NOT NULL DEFAULT 0,
  `expense_id` BIGINT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_labour_uuid` (`uuid`),
  KEY `idx_labour_project_id` (`project_id`),
  KEY `idx_labour_expense_id` (`expense_id`),
  KEY `idx_labour_deleted` (`deleted_at`),
  CONSTRAINT `fk_labour_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_labour_expense` FOREIGN KEY (`expense_id`) REFERENCES `expenses` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 13. Table: attachments
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `attachments`;
CREATE TABLE `attachments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` ENUM('expense', 'payment', 'invoice', 'project', 'client') NOT NULL,
  `entity_id` BIGINT UNSIGNED NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(100) DEFAULT NULL,
  `file_size` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attachments_entity` (`entity_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 14. Table: notifications
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('payment_due', 'overdue_invoice', 'labour_unpaid', 'daily_summary', 'system') NOT NULL DEFAULT 'system',
  `reference_id` BIGINT UNSIGNED DEFAULT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notifications_user` (`user_id`, `is_read`),
  CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 15. Table: audit_logs
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `action` ENUM('CREATE', 'UPDATE', 'DELETE', 'CANCEL', 'LOGIN') NOT NULL,
  `table_name` VARCHAR(100) NOT NULL,
  `record_id` BIGINT UNSIGNED DEFAULT NULL,
  `old_values` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_user` (`user_id`),
  KEY `idx_audit_table_record` (`table_name`, `record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 16. Table: sync_logs
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `sync_logs`;
CREATE TABLE `sync_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_uuid` VARCHAR(64) NOT NULL,
  `entity_name` VARCHAR(50) NOT NULL,
  `action` VARCHAR(20) NOT NULL,
  `sync_status` ENUM('success', 'conflicted', 'failed') NOT NULL DEFAULT 'success',
  `payload` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `error_message` TEXT DEFAULT NULL,
  `synced_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sync_client` (`client_uuid`),
  KEY `idx_sync_entity` (`entity_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SEED DATA
-- =====================================================================

-- 1. Default Admin / Owner User
-- Default password: Password@123
-- Default 4-digit PIN: 1234
INSERT INTO `users` (`id`, `uuid`, `name`, `email`, `phone`, `password_hash`, `pin_hash`, `role`, `status`)
VALUES (
  1,
  UUID(),
  'Contractor Owner',
  'admin@siteflow.webnovatech.co.in',
  '+919876543210',
  '$2y$10$BxKeQ4QKwodmvwHKGBg2DOzE9346aWpKq1teLaSVCHB1NjY6ASK.i', -- Password@123
  '$2y$10$NuTACwf.S8Y8jRx6FHT.BOADG.ykxD88lTOmTD3mzJlkUcpvFoPPG', -- 1234
  'owner',
  'active'
) ON DUPLICATE KEY UPDATE `password_hash`=VALUES(`password_hash`), `pin_hash`=VALUES(`pin_hash`);

-- 2. Business Settings
INSERT INTO `business_settings` (`id`, `business_name`, `owner_name`, `tagline`, `contact_number_1`, `contact_number_2`, `whatsapp_number`, `email`, `address_line1`, `address_line2`, `city`, `state`, `pincode`, `gstin`, `invoice_prefix`, `invoice_next_number`)
VALUES (
  1,
  'SiteFlow Construction & Plumbing Works',
  'Shri R. K. Sharma',
  'Every Site. Every Expense. Every Payment.',
  '+91 98250 12345',
  '+91 98250 67890',
  '+91 98250 12345',
  'contact@siteflow.webnovatech.co.in',
  'B-104, Shreemad Complex, Opp. GIDC',
  'Odhav Industrial Area',
  'Ahmedabad',
  'Gujarat',
  '382415',
  '24AAACS1234P1Z2',
  'SF-',
  1001
) ON DUPLICATE KEY UPDATE `id`=`id`;

-- 3. Default Expense Categories
INSERT INTO `expense_categories` (`id`, `name`, `description`, `icon_name`) VALUES
(1, 'Labour Charges', 'Wages paid to masons, plumbers, helpers and contractors', 'Engineering'),
(2, 'Plumbing Materials', 'Pipes, taps, elbows, couplings, valves and sealants', 'Plumbing'),
(3, 'Construction Materials', 'Bricks, gravel, aggregate and reinforcement steel', 'Construction'),
(4, 'Cement', 'OPC / PPC cement bags', 'Inventory2'),
(5, 'Sand', 'River sand, plaster sand and manufactured sand (M-sand)', 'Terrain'),
(6, 'Pipes and Fittings', 'PVC, CPVC, UPVC, GI pipes and bathroom fittings', 'WaterDamage'),
(7, 'Electrical Materials', 'Conduits, wiring, switches and fittings for site', 'ElectricalServices'),
(8, 'Transportation', 'Tempo, truck freight, auto charges and fuel', 'LocalShipping'),
(9, 'Equipment Rental', 'Mixer machine, scaffolding, drill machine and vibrator', 'Handyman'),
(10, 'Food and Refreshments', 'Tea, snacks and water for workers on site', 'Restaurant'),
(11, 'Maintenance', 'Tool sharpening, generator servicing and repairs', 'Build'),
(12, 'Miscellaneous', 'General site expenses and petty cash', 'ReceiptLong')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- 4. Initial Sample Client
INSERT INTO `clients` (`id`, `uuid`, `name`, `company_name`, `phone`, `whatsapp_number`, `email`, `billing_address`, `gstin`, `notes`)
VALUES (
  1,
  UUID(),
  'Rajesh Patel',
  'Patel Villa & Residency',
  '+91 98980 11223',
  '+91 98980 11223',
  'rajesh.patel@example.com',
  'Plot 45, Shantivan Bungalows, Bopal, Ahmedabad - 380058',
  '24AABCP5678Q1Z1',
  'Regular residential client. Very prompt in payment.'
) ON DUPLICATE KEY UPDATE `id`=`id`;

-- 5. Initial Sample Project
INSERT INTO `projects` (`id`, `uuid`, `project_code`, `project_name`, `client_id`, `client_name_cached`, `client_phone_cached`, `project_address`, `description`, `start_date`, `expected_completion_date`, `agreed_amount`, `advance_received`, `status`, `notes`)
VALUES (
  1,
  UUID(),
  'PRJ-2026-001',
  'Bopal Luxury Bungalow Plumbing & Renovation',
  1,
  'Rajesh Patel',
  '+91 98980 11223',
  'Plot 45, Shantivan Bungalows, Bopal, Ahmedabad',
  'Complete 4-bathroom concealed plumbing, solar water piping, drainage and external drainage chambers.',
  CURRENT_DATE - INTERVAL 15 DAY,
  CURRENT_DATE + INTERVAL 30 DAY,
  285000.00,
  50000.00,
  'Active',
  'All CPVC piping brand to be Astral. Fixtures Jaquar.'
) ON DUPLICATE KEY UPDATE `id`=`id`;

-- 6. Initial Sample Expenses for the Project
INSERT INTO `expenses` (`id`, `uuid`, `expense_code`, `expense_date`, `project_id`, `category_id`, `description`, `amount`, `payment_method`, `paid_to_vendor`, `notes`)
VALUES 
(1, UUID(), 'EXP-202611-0001', CURRENT_DATE - INTERVAL 10 DAY, 1, 2, 'Astral CPVC Pipes 1 inch and 0.75 inch, Fittings & Solvent', 38500.00, 'UPI', 'Shree Ram Hardware & Sanitary', 'Bill No. 4421'),
(2, UUID(), 'EXP-202611-0002', CURRENT_DATE - INTERVAL 7 DAY, 1, 1, 'Plumber 5 days wage for 2 senior plumbers and 2 helpers', 22000.00, 'Cash', 'Ramesh Mistry (Head Plumber)', 'Weekly settlement'),
(3, UUID(), 'EXP-202611-0003', CURRENT_DATE - INTERVAL 3 DAY, 1, 8, 'Tempo freight charges from hardware shop to site', 1500.00, 'Cash', 'Chhota Hathi Transport', 'Two trips')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- 7. Initial Sample Payment Received
INSERT INTO `payments` (`id`, `uuid`, `payment_code`, `client_id`, `project_id`, `payment_date`, `amount`, `payment_method`, `transaction_reference`, `description`, `notes`)
VALUES (
  1,
  UUID(),
  'PAY-202611-0001',
  1,
  1,
  CURRENT_DATE - INTERVAL 14 DAY,
  50000.00,
  'Bank Transfer',
  'NEFT-HDFC0098762',
  'Advance token payment for starting concealed plumbing work',
  'Credited to SBI account'
) ON DUPLICATE KEY UPDATE `id`=`id`;

-- 8. Initial Sample Invoice
INSERT INTO `invoices` (`id`, `uuid`, `invoice_number`, `invoice_date`, `due_date`, `client_id`, `project_id`, `work_description`, `subtotal`, `discount_amount`, `tax_percent`, `tax_amount`, `grand_total`, `previous_advance_adjusted`, `amount_paid`, `balance_due`, `status`)
VALUES (
  1,
  UUID(),
  'SF-1001',
  CURRENT_DATE - INTERVAL 5 DAY,
  CURRENT_DATE + INTERVAL 5 DAY,
  1,
  1,
  'First Running Bill for Concealed Plumbing and Drainage Lines at Plot 45, Shantivan Bungalows',
  120000.00,
  0.00,
  0.00,
  0.00,
  120000.00,
  50000.00,
  50000.00,
  70000.00,
  'Partially Paid'
) ON DUPLICATE KEY UPDATE `id`=`id`;

INSERT INTO `invoice_items` (`id`, `invoice_id`, `item_description`, `quantity`, `unit`, `rate`, `amount`, `sort_order`) VALUES
(1, 1, 'Concealed CPVC pipeline installation with pressure testing (4 Bathrooms)', 4.00, 'Point/Bath', 15000.00, 60000.00, 1),
(2, 1, 'SWR Drainage vertical stacks & inspection chambers connection', 1.00, 'Lump Sum', 35000.00, 35000.00, 2),
(3, 1, 'Core cutting and chasing in brick walls including plaster patchup', 1.00, 'Lump Sum', 25000.00, 25000.00, 3)
ON DUPLICATE KEY UPDATE `id`=`id`;

INSERT INTO `invoice_payments` (`id`, `invoice_id`, `payment_id`, `amount_allocated`, `notes`)
VALUES (1, 1, 1, 50000.00, 'Adjusted from initial project advance')
ON DUPLICATE KEY UPDATE `id`=`id`;