-- Vendor Payment Tracking System - MySQL Database Schema
-- Generated for CodeIgniter 4 + Flutter Mobile App + Firebase Notifications
-- MySQL Version: 8.0+

SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- CREATE DATABASE IF NOT EXISTS vendor_payment_tracker
--   CHARACTER SET utf8mb4
--   COLLATE utf8mb4_unicode_ci;
-- USE vendor_payment_tracker;

-- =====================================================
-- 1. USERS, ROLES AND PERMISSIONS
-- =====================================================

DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS notification_deliveries;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS user_devices;
DROP TABLE IF EXISTS payment_transactions;
DROP TABLE IF EXISTS payment_request_approval_attachments;
DROP TABLE IF EXISTS payment_request_approval_logs;
DROP TABLE IF EXISTS payment_request_approvals;
DROP TABLE IF EXISTS payment_request_status_logs;
DROP TABLE IF EXISTS payment_request_attachments;
DROP TABLE IF EXISTS payment_request_work_details;
DROP TABLE IF EXISTS payment_requests;
DROP TABLE IF EXISTS project_account_approval_users;
DROP TABLE IF EXISTS project_account_approval_levels;
DROP TABLE IF EXISTS project_account_incharges;
DROP TABLE IF EXISTS project_accounts;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS vendors;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS system_settings;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_code VARCHAR(50) NULL UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  mobile VARCHAR(20) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  user_type ENUM('SUPER_ADMIN','WEB_ADMIN','WEB_USER','APP_USER') NOT NULL DEFAULT 'APP_USER',
  designation VARCHAR(100) NULL,
  department VARCHAR(100) NULL,
  profile_image_path VARCHAR(500) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_users_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_users_user_type (user_type),
  INDEX idx_users_active (is_active),
  INDEX idx_users_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Stores all web admin users and mobile app users.';

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_name VARCHAR(100) NOT NULL UNIQUE,
  role_key VARCHAR(100) NOT NULL UNIQUE,
  description TEXT NULL,
  role_scope ENUM('WEB','APP','BOTH') NOT NULL DEFAULT 'BOTH',
  is_system_role TINYINT(1) NOT NULL DEFAULT 0,
  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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Role master for role based access control.';

CREATE TABLE permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_key VARCHAR(100) NOT NULL,
  permission_key VARCHAR(150) NOT NULL UNIQUE,
  permission_name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_permissions_module (module_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Permission master, e.g. projects.create, vendors.view, payments.process.';

CREATE TABLE role_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_role_permission (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Maps permissions to roles.';

CREATE TABLE user_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  assigned_by BIGINT UNSIGNED NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_role (user_id, role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Many-to-many mapping between users and roles.';

-- =====================================================
-- 2. VENDOR / SUPPLIER MASTER
-- =====================================================

CREATE TABLE vendors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vendor_code VARCHAR(50) NOT NULL UNIQUE,
  vendor_name VARCHAR(200) NOT NULL,
  vendor_type ENUM('VENDOR','SUPPLIER','CONTRACTOR','CONSULTANT','OTHER') NOT NULL DEFAULT 'VENDOR',
  contact_person VARCHAR(150) NULL,
  mobile VARCHAR(20) NULL,
  alternate_mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  gst_number VARCHAR(30) NULL,
  pan_number VARCHAR(20) NULL,
  address_line1 VARCHAR(255) NULL,
  address_line2 VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pincode VARCHAR(20) NULL,
  country VARCHAR(100) NOT NULL DEFAULT 'India',
  bank_name VARCHAR(150) NULL,
  bank_account_number VARCHAR(50) NULL,
  bank_ifsc_code VARCHAR(20) NULL,
  bank_account_holder_name VARCHAR(150) NULL,
  payment_terms TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_vendors_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_vendors_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_vendors_name (vendor_name),
  INDEX idx_vendors_gst (gst_number),
  INDEX idx_vendors_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Master directory for vendors, suppliers and contractors.';

-- =====================================================
-- 3. PROJECTS AND ACCOUNT-WISE APPROVAL CONFIGURATION
-- =====================================================

CREATE TABLE projects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_code VARCHAR(50) NOT NULL UNIQUE,
  project_name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  location VARCHAR(255) NULL,
  address TEXT NULL,
  start_date DATE NULL,
  expected_end_date DATE NULL,
  actual_end_date DATE NULL,
  estimated_budget DECIMAL(15,2) NULL,
  project_status ENUM('PLANNED','ACTIVE','ON_HOLD','COMPLETED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_projects_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_projects_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_projects_status (project_status),
  INDEX idx_projects_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Construction project master.';

CREATE TABLE project_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  account_code VARCHAR(50) NOT NULL,
  account_name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_project_account_code (project_id, account_code),
  CONSTRAINT fk_project_accounts_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_project_accounts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_project_accounts_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_project_accounts_project (project_id),
  INDEX idx_project_accounts_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Accounts (departments) under a project; each has its own approval chain.';

CREATE TABLE project_account_incharges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  assigned_from DATE NULL,
  assigned_to DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  assigned_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_incharge (account_id, user_id),
  CONSTRAINT fk_account_incharges_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_incharges_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_incharges_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_account_incharges_user (user_id),
  INDEX idx_account_incharges_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Users who may raise payment letters for an account.';

CREATE TABLE project_account_approval_levels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  level_name VARCHAR(100) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_level (account_id, level_number),
  CONSTRAINT fk_account_levels_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_levels_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_account_levels_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_account_levels_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Ordered approval levels for an account; approvers live in child table.';

CREATE TABLE project_account_approval_users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  approval_level_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  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,
  UNIQUE KEY uq_account_level_user (approval_level_id, user_id),
  CONSTRAINT fk_account_level_users_level FOREIGN KEY (approval_level_id) REFERENCES project_account_approval_levels(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_level_users_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_account_level_users_user (user_id),
  INDEX idx_account_level_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Approvers mapped to an account approval level (any one may complete the level).';

-- =====================================================
-- 4. PAYMENT REQUEST ENTRY AND ATTACHMENTS
-- =====================================================

CREATE TABLE payment_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_no VARCHAR(80) NOT NULL UNIQUE,
  project_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  vendor_id BIGINT UNSIGNED NOT NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  invoice_no VARCHAR(100) NULL,
  invoice_date DATE NULL,
  invoice_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  ra_bill_number INT UNSIGNED NULL COMMENT 'RA bill serial; unique per project+vendor; set on first submit',
  current_level INT UNSIGNED NOT NULL DEFAULT 0,
  max_level INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('DRAFT','SUBMITTED','UNDER_APPROVAL','REJECTED','AMENDMENT_REQUIRED','APPROVED','PAYMENT_PROCESSING','PARTIALLY_PAID','PAID','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  submitted_at DATETIME NULL,
  approved_at DATETIME NULL,
  rejected_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_payment_requests_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_requests_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_requests_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_requests_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_requests_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_payment_requests_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_payment_requests_project_vendor_ra (project_id, vendor_id, ra_bill_number),
  INDEX idx_payment_requests_project (project_id),
  INDEX idx_payment_requests_account (account_id),
  INDEX idx_payment_requests_project_account (project_id, account_id),
  INDEX idx_payment_requests_vendor (vendor_id),
  INDEX idx_payment_requests_ra_bill (project_id, vendor_id, ra_bill_number),
  INDEX idx_payment_requests_requested_by (requested_by),
  INDEX idx_payment_requests_status (status),
  INDEX idx_payment_requests_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Main payment request header raised from mobile app or admin panel.';

CREATE TABLE payment_request_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  attachment_type ENUM('INVOICE','WORK_PHOTO','WORK_ORDER','MEASUREMENT_SHEET','APPROVAL_DOC','OTHER') NOT NULL DEFAULT 'OTHER',
  file_name VARCHAR(255) NOT NULL,
  original_file_name VARCHAR(255) NULL,
  file_path VARCHAR(600) NOT NULL,
  file_url VARCHAR(800) NULL,
  mime_type VARCHAR(100) NULL,
  file_size_bytes BIGINT UNSIGNED NULL,
  uploaded_by BIGINT UNSIGNED NOT NULL,
  uploaded_from ENUM('WEB','APP') NOT NULL DEFAULT 'APP',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_attachments_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_attachments_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_attachments_request (payment_request_id),
  INDEX idx_attachments_type (attachment_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Documents and images attached to payment requests.';

CREATE TABLE payment_request_status_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  previous_status VARCHAR(50) NULL,
  new_status VARCHAR(50) NOT NULL,
  action_by BIGINT UNSIGNED NOT NULL,
  action_source ENUM('WEB','APP','SYSTEM') NOT NULL DEFAULT 'APP',
  remarks TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_status_logs_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_status_logs_action_by FOREIGN KEY (action_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_status_logs_request (payment_request_id),
  INDEX idx_status_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Every request status transition for audit and timeline display.';

-- =====================================================
-- 5. APPROVAL WORKFLOW RUNTIME TABLES
-- =====================================================

CREATE TABLE payment_request_approvals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  account_approval_level_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('PENDING','APPROVED','REJECTED','SKIPPED','RETURNED_FOR_AMENDMENT') NOT NULL DEFAULT 'PENDING',
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acted_at DATETIME NULL,
  remarks TEXT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_request_approvals_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_request_approvals_level FOREIGN KEY (account_approval_level_id) REFERENCES project_account_approval_levels(id) ON DELETE RESTRICT,
  CONSTRAINT fk_request_approvals_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  UNIQUE KEY uq_request_level_approver (payment_request_id, level_number, approver_user_id),
  INDEX idx_request_approvals_user_status (approver_user_id, status),
  INDEX idx_request_approvals_current (is_current),
  INDEX idx_request_approvals_level (payment_request_id, level_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='One row per assignee per level; any one APPROVED completes the level (peers SKIPPED).';

CREATE TABLE payment_request_approval_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  approval_id BIGINT UNSIGNED NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  action ENUM('ASSIGNED','APPROVED','REJECTED','RETURNED_FOR_AMENDMENT','RESUBMITTED','FORWARDED','AUTO_SKIPPED') NOT NULL,
  remarks TEXT NULL,
  previous_level INT UNSIGNED NULL,
  next_level INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_logs_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_logs_approval FOREIGN KEY (approval_id) REFERENCES payment_request_approvals(id) ON DELETE SET NULL,
  CONSTRAINT fk_approval_logs_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_approval_logs_request (payment_request_id),
  INDEX idx_approval_logs_user (approver_user_id),
  INDEX idx_approval_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Immutable approval action trail with remarks and level movement.';

CREATE TABLE payment_request_approval_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  approval_id BIGINT UNSIGNED NOT NULL,
  approval_log_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  action ENUM('APPROVED','REJECTED','RETURNED_FOR_AMENDMENT') NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_file_name VARCHAR(255) NULL,
  file_path VARCHAR(600) NOT NULL,
  file_url VARCHAR(800) NULL,
  mime_type VARCHAR(100) NULL,
  file_size_bytes BIGINT UNSIGNED NULL,
  remarks TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_attach_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_approval FOREIGN KEY (approval_id) REFERENCES payment_request_approvals(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_log FOREIGN KEY (approval_log_id) REFERENCES payment_request_approval_logs(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_approval_attach_request (payment_request_id),
  INDEX idx_approval_attach_approval (approval_id),
  INDEX idx_approval_attach_log (approval_log_id),
  INDEX idx_approval_attach_level (payment_request_id, level_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Optional files uploaded by approvers when acting on a level.';

-- =====================================================
-- 6. ACCOUNTING / PAYMENT PROCESSING
-- =====================================================

CREATE TABLE payment_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  transaction_no VARCHAR(100) NULL UNIQUE,
  payment_amount DECIMAL(15,2) NOT NULL,
  payment_date DATE NOT NULL,
  payment_mode ENUM('NEFT','RTGS','IMPS','UPI','CHEQUE','CASH','OTHER') NOT NULL DEFAULT 'NEFT',
  bank_name VARCHAR(150) NULL,
  reference_no VARCHAR(150) NULL,
  cheque_no VARCHAR(100) NULL,
  cheque_date DATE NULL,
  payment_status ENUM('PROCESSING','SUCCESS','FAILED','CANCELLED') NOT NULL DEFAULT 'SUCCESS',
  accounting_remarks TEXT NULL,
  processed_by BIGINT UNSIGNED NOT NULL,
  processed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_transactions_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_transactions_processed_by FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_payment_transactions_request (payment_request_id),
  INDEX idx_payment_transactions_date (payment_date),
  INDEX idx_payment_transactions_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Payment disbursement records entered by accounts team after approval.';

-- =====================================================
-- 7. FIREBASE DEVICES AND NOTIFICATIONS
-- =====================================================

CREATE TABLE user_devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  device_uuid VARCHAR(150) NULL,
  platform ENUM('ANDROID','IOS','WEB') NOT NULL,
  fcm_token VARCHAR(500) NOT NULL,
  app_version VARCHAR(50) NULL,
  device_model VARCHAR(150) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fcm_token (fcm_token),
  CONSTRAINT fk_user_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_devices_user (user_id),
  INDEX idx_user_devices_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Mobile/web device tokens for Firebase push notifications.';

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  payment_request_id BIGINT UNSIGNED NULL,
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  notification_type ENUM('REQUEST_SUBMITTED','APPROVAL_PENDING','REQUEST_APPROVED','REQUEST_REJECTED','AMENDMENT_REQUIRED','PAYMENT_PROCESSED','GENERAL') NOT NULL DEFAULT 'GENERAL',
  data_json JSON NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notifications_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE SET NULL,
  INDEX idx_notifications_user_read (user_id, is_read),
  INDEX idx_notifications_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='In-app notification inbox.';

CREATE TABLE notification_deliveries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  notification_id BIGINT UNSIGNED NOT NULL,
  user_device_id BIGINT UNSIGNED NULL,
  fcm_token VARCHAR(500) NULL,
  delivery_status ENUM('PENDING','SENT','FAILED') NOT NULL DEFAULT 'PENDING',
  firebase_message_id VARCHAR(255) NULL,
  error_message TEXT NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notification_deliveries_notification FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  CONSTRAINT fk_notification_deliveries_device FOREIGN KEY (user_device_id) REFERENCES user_devices(id) ON DELETE SET NULL,
  INDEX idx_notification_deliveries_status (delivery_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Firebase delivery tracking for push notifications.';

-- =====================================================
-- 8. GLOBAL AUDIT AND SETTINGS
-- =====================================================

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  module_name VARCHAR(100) NOT NULL,
  entity_name VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  old_values JSON NULL,
  new_values JSON NULL,
  ip_address VARCHAR(50) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_logs_entity (entity_name, entity_id),
  INDEX idx_audit_logs_user (user_id),
  INDEX idx_audit_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Generic audit log for important admin and app actions.';

CREATE TABLE system_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(150) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  setting_type ENUM('STRING','NUMBER','BOOLEAN','JSON') NOT NULL DEFAULT 'STRING',
  description TEXT NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_system_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Application-level configurable settings.';

-- =====================================================
-- 9. STARTER DATA
-- =====================================================

INSERT INTO roles (role_name, role_key, description, role_scope, is_system_role) VALUES
('Super Admin', 'SUPER_ADMIN', 'Full system access.', 'WEB', 1),
('Accounts User', 'ACCOUNTS_USER', 'Can process approved payment requests.', 'WEB', 1),
('Project Incharge', 'PROJECT_INCHARGE', 'Can create and submit project-wise payment requests from mobile app.', 'APP', 1),
('Approver', 'APPROVER', 'Can approve or reject requests based on project approval level.', 'APP', 1);

INSERT INTO permissions (module_key, permission_key, permission_name) VALUES
('users', 'users.view', 'View Users'),
('users', 'users.create', 'Create Users'),
('users', 'users.update', 'Update Users'),
('roles', 'roles.manage', 'Manage Roles and Permissions'),
('vendors', 'vendors.view', 'View Vendors'),
('vendors', 'vendors.create', 'Create Vendors'),
('vendors', 'vendors.update', 'Update Vendors'),
('projects', 'projects.view', 'View Projects'),
('projects', 'projects.create', 'Create Projects'),
('projects', 'projects.update', 'Update Projects'),
('projects', 'projects.approval_levels', 'Manage Project Approval Levels'),
('payment_requests', 'payment_requests.view', 'View Payment Requests'),
('payment_requests', 'payment_requests.create', 'Create Payment Requests'),
('payment_requests', 'payment_requests.update', 'Update Payment Requests'),
('payment_requests', 'payment_requests.approve', 'Approve Payment Requests'),
('payments', 'payments.process', 'Process Payments'),
('reports', 'reports.view', 'View Reports');

INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('payment_request_prefix', 'PR', 'STRING', 'Prefix used while generating payment request numbers.'),
('payment_transaction_prefix', 'PAY', 'STRING', 'Prefix used while generating payment transaction numbers.'),
('allow_request_edit_after_submission', 'false', 'BOOLEAN', 'Whether request can be edited after submission without rejection/amendment.'),
('default_currency', 'INR', 'STRING', 'Default application currency.');

SET FOREIGN_KEY_CHECKS = 1;