-- ========================================================
-- Enterprise Cash Management Software DB Installation
-- Native MySQL DDL and Seed Data Script
-- ========================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `advance_adjustments`;
DROP TABLE IF EXISTS `cash_advances`;
DROP TABLE IF EXISTS `shift_closings`;
DROP TABLE IF EXISTS `cash_transactions`;
DROP TABLE IF EXISTS `bank_accounts`;
DROP TABLE IF EXISTS `branches`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `companies`;
DROP TABLE IF EXISTS `activity_logs`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. COMPANIES TABLE
CREATE TABLE `companies` (
  `id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `logo` VARCHAR(10) DEFAULT '💼',
  `subscription` ENUM('Basic', 'Enterprise', 'Premium') DEFAULT 'Basic',
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT '৳',
  `tax_number` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Companies
INSERT INTO `companies` (`id`, `name`, `logo`, `subscription`, `status`, `phone`, `email`, `address`, `currency`, `tax_number`) VALUES
('COMP-001', 'Teletalk Corp Ltd', '⚡', 'Enterprise', 'Active', '+88029555111', 'info@teletalkcorp.com', 'Gulshan-2, Dhaka 1212', '৳', 'BIN-19284756302'),
('COMP-002', 'Globe Freight Systems', '🌐', 'Premium', 'Active', '+14155552671', 'finance@globefreight.com', 'Sansom St, San Francisco, CA', '$', 'EIN-99238812'),
('COMP-003', 'AeroLine Aviation', '✈️', 'Basic', 'Inactive', '+442079460192', 'accounts@aeroline.co.uk', 'Heathrow Corporate Hub, London', '£', 'VAT-UK99824');


-- 2. BRANCHES TABLE
CREATE TABLE `branches` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `balance` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `opening_balance` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `manager_name` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Branches
INSERT INTO `branches` (`id`, `company_id`, `name`, `code`, `balance`, `opening_balance`, `manager_name`, `status`) VALUES
('BR-101', 'COMP-001', 'Head Office (Dhaka)', 'HQ-DKA', 1258750.00, 1000000.00, 'Ariful Islam', 'Active'),
('BR-102', 'COMP-001', 'Uttara Branch', 'BR-UTR', 458750.00, 400000.00, 'Tasnim Jahan', 'Active'),
('BR-103', 'COMP-001', 'Dhanmondi Branch', 'BR-DMD', 325500.00, 300000.00, 'Imran Chowdhury', 'Active'),
('BR-104', 'COMP-001', 'Chittagong Port Branch', 'BR-CTG', 285250.00, 250000.00, 'Zubair Al-Hasan', 'Active'),
('BR-105', 'COMP-001', 'Sylhet Branch', 'BR-SYL', 130500.00, 100000.00, 'Fahmida Kamal', 'Active');


-- 3. USERS TABLE
CREATE TABLE `users` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `branch_id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('Super Admin', 'Company Admin', 'Cashier', 'Accountant', 'Auditor') NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT '1',
  `photo_url` TEXT DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `designation` VARCHAR(100) DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_unique` (`email`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Users with designations, photo URLs, bios, etc.
INSERT INTO `users` (`id`, `company_id`, `branch_id`, `name`, `email`, `password`, `role`, `is_active`, `photo_url`, `phone`, `designation`, `bio`) VALUES
('USR-001', 'COMP-001', 'BR-101', 'M. Ashikur Rahman', 'mdashikurcse@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', 1, 'https://images.unsplash.com/photo-1535713875002-d1d0cf377fde?auto=format&fit=crop&w=150&q=80', '+8801700000001', 'SaaS platform Architect', 'Head of global cash flow, cloud deployment & multi-tenant security structures.'),
('USR-002', 'COMP-001', 'BR-101', 'Rashedul Hasan', 'rashed@teletalkcorp.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Company Admin', 1, 'https://images.unsplash.com/photo-1570295999919-56ceb5ecca61?auto=format&fit=crop&w=150&q=80', '+8801811112222', 'Finance Director', 'Managing corporate audits, legal tax clearances, and organizational branch liquidity controllers.'),
('USR-003', 'COMP-001', 'BR-102', 'Anika Tabassum', 'anika@teletalkcorp.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Cashier', 1, 'https://images.unsplash.com/photo-1494790108377-be9c29b29330?auto=format&fit=crop&w=150&q=80', '+8801999888777', 'Senior Petty Cash Cashier', 'Registrar of cash payments & operational vouchers.'),
('USR-004', 'COMP-001', 'BR-101', 'Kazi Mahbub', 'mahbub@teletalkcorp.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Accountant', 1, 'https://images.unsplash.com/photo-1472099645785-5658abf4ff4e?auto=format&fit=crop&w=150&q=80', '+8801555000999', 'Chief General Ledger Accountant', 'Supervisor of monthly ledger reconciliations and financial bank adjustment worksheets.');


-- 4. BANK ACCOUNTS
CREATE TABLE `bank_accounts` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `bank_name` VARCHAR(150) NOT NULL,
  `account_name` VARCHAR(150) NOT NULL,
  `account_number` VARCHAR(100) NOT NULL,
  `branch` VARCHAR(150) NOT NULL,
  `balance` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Bank Accounts
INSERT INTO `bank_accounts` (`id`, `company_id`, `bank_name`, `account_name`, `account_number`, `branch`, `balance`, `status`) VALUES
('BNK-001', 'COMP-001', 'Sonali Bank PLC', 'Teletalk Principal Operating', '0021-992384-012', 'Motijheel Corporate Branch', 4500000.00, 'Active'),
('BNK-002', 'COMP-001', 'Dutch-Bangla Bank', 'Teletalk Petty Cash Pool', '122.105.992834', 'Gulshan Branch', 1250000.00, 'Active'),
('BNK-003', 'COMP-001', 'City Bank PLC', 'Teletalk Emergency Reserve', '5120-192847-111', 'Banani Branch', 450000.00, 'Active');


-- 5. CASH TRANSACTIONS
CREATE TABLE `cash_transactions` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `branch_id` VARCHAR(50) NOT NULL,
  `voucher_no` VARCHAR(100) NOT NULL,
  `type` ENUM('Cash In', 'Cash Out', 'Transfer', 'Contra', 'Journal', 'Expense', 'Income', 'Deposit', 'Withdrawal') NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `particulars` TEXT NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `payment_method` ENUM('Cash', 'Bank', 'Cheque') NOT NULL DEFAULT 'Cash',
  `bank_account_id` VARCHAR(50) DEFAULT NULL,
  `reference_no` VARCHAR(100) DEFAULT NULL,
  `attachment_name` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('Pending', 'Approved', 'Completed', 'Rejected') NOT NULL DEFAULT 'Pending',
  `created_by_name` VARCHAR(100) NOT NULL,
  `created_by_id` VARCHAR(50) NOT NULL,
  `approval_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `voucher_unique` (`voucher_no`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Transactions
INSERT INTO `cash_transactions` (`id`, `company_id`, `branch_id`, `voucher_no`, `type`, `amount`, `particulars`, `category`, `payment_method`, `bank_account_id`, `reference_no`, `status`, `created_by_name`, `created_by_id`) VALUES
('TX-201', 'COMP-001', 'BR-101', 'VCH-2026-601', 'Cash In', 12500.00, 'Utility reimbursement refund received from tenant floor', 'Miscellaneous Revenue', 'Cash', NULL, 'REF-9923', 'Approved', 'Anika Tabassum', 'USR-003'),
('TX-202', 'COMP-001', 'BR-101', 'VCH-2026-602', 'Cash Out', 45000.00, 'Purchase of server UPS backup for HQ rack server', 'Office Equipment', 'Cash', NULL, 'REF-1122', 'Approved', 'Anika Tabassum', 'USR-003'),
('TX-203', 'COMP-001', 'BR-102', 'VCH-2026-603', 'Cash In', 55000.00, 'Bulk invoice fee collection for Broadband infrastructure', 'Broadband Revenue', 'Cash', NULL, 'REF-2233', 'Approved', 'Anika Tabassum', 'USR-003');


-- 6. SHIFT CLOSINGS TABLE
CREATE TABLE `shift_closings` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `branch_id` VARCHAR(50) NOT NULL,
  `date` DATE NOT NULL,
  `opening_balance` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `total_cash_in` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `total_cash_out` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `calculated_closing` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `actual_closing` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `mismatch_amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `notes` TEXT DEFAULT NULL,
  `closed_by_name` VARCHAR(100) NOT NULL,
  `status` ENUM('Open', 'Closed') DEFAULT 'Closed',
  `closed_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 7. CASH ADVANCES & EMPLOYEES REGISTER
CREATE TABLE `cash_advances` (
  `id` VARCHAR(50) NOT NULL,
  `company_id` VARCHAR(50) NOT NULL,
  `branch_id` VARCHAR(50) NOT NULL,
  `voucher_no` VARCHAR(100) NOT NULL,
  `employee_name` VARCHAR(150) NOT NULL,
  `employee_designation` VARCHAR(100) DEFAULT NULL,
  `employee_phone` VARCHAR(50) DEFAULT NULL,
  `purpose` TEXT NOT NULL,
  `advance_amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `adjusted_amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `cash_refunded` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `cash_due_paid` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
  `status` ENUM('Pending', 'Partially Adjusted', 'Fully Adjusted') DEFAULT 'Pending',
  `created_by_id` VARCHAR(50) NOT NULL,
  `created_by_name` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserting Sample Cash Advance
INSERT INTO `cash_advances` (`id`, `company_id`, `branch_id`, `voucher_no`, `employee_name`, `employee_designation`, `employee_phone`, `purpose`, `advance_amount`, `adjusted_amount`, `status`, `created_by_id`, `created_by_name`) VALUES
('ADV-1001', 'COMP-001', 'BR-101', 'ADV-2026-901', 'Kamrul Hasan', 'Senior Field Sales Representative', '+8801755102030', 'Travel, accommodation and lunch allowances for Uttara division', 25000.00, 15000.00, 'Partially Adjusted', 'USR-001', 'M. Ashikur Rahman');


-- 8. ADVANCE ADJUSTMENTS DETAILS
CREATE TABLE `advance_adjustments` (
  `id` VARCHAR(50) NOT NULL,
  `cash_advance_id` VARCHAR(50) NOT NULL,
  `expense_category` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `description` TEXT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`cash_advance_id`) REFERENCES `cash_advances` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `advance_adjustments` (`id`, `cash_advance_id`, `expense_category`, `amount`, `description`) VALUES
('ADJ-501', 'ADV-1001', 'Travel Expense', 10000.00, 'Dhaka to Sylhet return Train ticket with client'),
('ADJ-502', 'ADV-1001', 'Entertainment', 5000.00, 'Client lunch meeting bill at Uttara Club');


-- 9. ACTIVITY AUDIT TRAIL LOGS (IMMUTABLE)
CREATE TABLE `activity_logs` (
  `id` VARCHAR(50) NOT NULL,
  `user_id` VARCHAR(50) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `role` VARCHAR(50) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `module` VARCHAR(100) NOT NULL,
  `description` TEXT NOT NULL,
  `old_value` TEXT DEFAULT NULL,
  `new_value` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(50) DEFAULT '127.0.0.1',
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `activity_logs` (`id`, `user_id`, `user_name`, `role`, `action`, `module`, `description`) VALUES
('LOG-101', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'CREATE_TENANT', 'TENANTS', 'Configured Teletalk Corp Ltd as Enterprise SaaS customer in system.'),
('LOG-102', 'USR-001', 'M. Ashikur Rahman', 'Super Admin', 'TOGGLE_PROFILE_EDIT_ACCESS', 'SUPER_ADMIN', 'Granted universal profile modification authorizations to standard users.');

SET FOREIGN_KEY_CHECKS = 1;