-- ============================================================
-- NGO MIS - Complete Database Schema
-- Phase 1 | MySQL 8.0+
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ============================================================
-- 1. DEPARTMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 2. ROLES
-- ============================================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `description` TEXT,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 3. PERMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module` VARCHAR(100) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 4. ROLE PERMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission_unique` (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 5. USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `mobile` VARCHAR(15) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `designation` VARCHAR(100),
  `department_id` BIGINT UNSIGNED,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `user_type` ENUM('super_admin','admin','manager','csr_manager','accountant','volunteer','staff') NOT NULL DEFAULT 'staff',
  `profile_image` VARCHAR(255),
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `last_login_at` TIMESTAMP NULL,
  `last_login_ip` VARCHAR(45),
  `remember_token` VARCHAR(100),
  `password_reset_token` VARCHAR(255),
  `password_reset_expires` TIMESTAMP NULL,
  `email_verified_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ============================================================
-- 6. USER PERMISSIONS (individual overrides)
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  `granted` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_permission_unique` (`user_id`, `permission_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 7. DONORS
-- ============================================================
CREATE TABLE IF NOT EXISTS `donors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `donor_id` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `father_name` VARCHAR(100),
  `email` VARCHAR(150),
  `mobile` VARCHAR(15) NOT NULL,
  `dob` DATE,
  `anniversary_date` DATE,
  `gender` ENUM('male','female','other'),
  `aadhaar_number` VARCHAR(12),
  `aadhaar_file` VARCHAR(255),
  `pan_number` VARCHAR(10),
  `pan_file` VARCHAR(255),
  `address` TEXT,
  `city` VARCHAR(100),
  `state` VARCHAR(100),
  `pincode` VARCHAR(10),
  `total_donations` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `last_donation_date` DATE,
  `donation_count` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 8. DONATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `donations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `donation_id` VARCHAR(30) NOT NULL UNIQUE,
  `receipt_number` VARCHAR(30) NOT NULL UNIQUE,
  `donor_id` BIGINT UNSIGNED,
  `donor_name` VARCHAR(100) NOT NULL,
  `donor_email` VARCHAR(150),
  `donor_mobile` VARCHAR(15) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `purpose` VARCHAR(255),
  `additional_message` TEXT,
  `payment_gateway` ENUM('razorpay','stripe','manual','cash','cheque','upi') NOT NULL DEFAULT 'razorpay',
  `payment_id` VARCHAR(100),
  `transaction_id` VARCHAR(100),
  `payment_status` ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `payment_mode` VARCHAR(50),
  `donation_date` DATE NOT NULL,
  `receipt_generated` TINYINT(1) NOT NULL DEFAULT 0,
  `receipt_file` VARCHAR(255),
  `aadhaar_number` VARCHAR(12),
  `aadhaar_file` VARCHAR(255),
  `pan_number` VARCHAR(10),
  `pan_file` VARCHAR(255),
  `father_name` VARCHAR(100),
  `dob` DATE,
  `anniversary_date` DATE,
  `address` TEXT,
  `processed_by` BIGINT UNSIGNED,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`donor_id`) REFERENCES `donors`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 9. SUBSCRIPTION DONATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subscription_id` VARCHAR(30) NOT NULL UNIQUE,
  `donor_id` BIGINT UNSIGNED,
  `full_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `mobile` VARCHAR(15) NOT NULL,
  `address` TEXT,
  `amount` DECIMAL(10,2) NOT NULL,
  `frequency` ENUM('monthly','quarterly','half_yearly','yearly') NOT NULL,
  `start_date` DATE NOT NULL,
  `next_payment_date` DATE NOT NULL,
  `status` ENUM('active','paused','cancelled','failed','completed') NOT NULL DEFAULT 'active',
  `gateway_subscription_id` VARCHAR(100),
  `total_collected` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `payment_count` INT NOT NULL DEFAULT 0,
  `last_payment_date` DATE,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`donor_id`) REFERENCES `donors`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 10. SUBSCRIPTION PAYMENTS (individual payments)
-- ============================================================
CREATE TABLE IF NOT EXISTS `subscription_payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subscription_id` BIGINT UNSIGNED NOT NULL,
  `donation_id` BIGINT UNSIGNED,
  `amount` DECIMAL(10,2) NOT NULL,
  `payment_id` VARCHAR(100),
  `status` ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  `payment_date` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`donation_id`) REFERENCES `donations`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 11. CSR COMPANIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `csr_companies` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_code` VARCHAR(20) NOT NULL UNIQUE,
  `company_name` VARCHAR(150) NOT NULL,
  `company_type` VARCHAR(100),
  `registration_number` VARCHAR(100),
  `website_url` VARCHAR(255),
  `contact_person_name` VARCHAR(100),
  `contact_email` VARCHAR(150),
  `contact_phone` VARCHAR(15),
  `contact_position` VARCHAR(100),
  `csr_budget` DECIMAL(15,2),
  `csr_goals` TEXT,
  `csr_report_file` VARCHAR(255),
  `address` TEXT,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 12. PROJECT CATEGORIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `project_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `slug` VARCHAR(110) NOT NULL UNIQUE,
  `description` TEXT,
  `icon` VARCHAR(100),
  `color` VARCHAR(20),
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 13. CSR PROJECTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `projects` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_code` VARCHAR(20) NOT NULL UNIQUE,
  `project_title` VARCHAR(200) NOT NULL,
  `csr_company_id` BIGINT UNSIGNED NOT NULL,
  `category_id` BIGINT UNSIGNED,
  `description` TEXT,
  `start_date` DATE,
  `end_date` DATE,
  `location` VARCHAR(255),
  `budget_allocation` DECIMAL(15,2),
  `total_spent` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `remaining_budget` DECIMAL(15,2),
  `stakeholders` TEXT,
  `expected_impact` TEXT,
  `funding_source` VARCHAR(255),
  `is_public` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('draft','pending','approved','active','completed','cancelled') NOT NULL DEFAULT 'draft',
  `approved_by` BIGINT UNSIGNED,
  `approved_at` TIMESTAMP NULL,
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`csr_company_id`) REFERENCES `csr_companies`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`category_id`) REFERENCES `project_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 14. PROJECT ATTACHMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `project_attachments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` BIGINT UNSIGNED NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(50),
  `file_size` INT,
  `description` VARCHAR(255),
  `attachment_type` ENUM('proposal','approval','budget','image','report','other') NOT NULL DEFAULT 'other',
  `uploaded_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 15. PROJECT EXPENSES
-- ============================================================
CREATE TABLE IF NOT EXISTS `project_expenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `expense_id` VARCHAR(20) NOT NULL UNIQUE,
  `project_id` BIGINT UNSIGNED NOT NULL,
  `expense_date` DATE NOT NULL,
  `category` ENUM('material','transportation','food','staff','medical','equipment','administration','other') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `description` TEXT,
  `payment_mode` ENUM('cash','cheque','bank_transfer','upi','card','other') NOT NULL DEFAULT 'cash',
  `vendor_name` VARCHAR(150),
  `bill_number` VARCHAR(100),
  `bill_file` VARCHAR(255),
  `attachment` VARCHAR(255),
  `is_override` TINYINT(1) NOT NULL DEFAULT 0,
  `override_approved_by` BIGINT UNSIGNED,
  `override_reason` TEXT,
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`override_approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 16. PRODUCTS / INVENTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS `products` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_code` VARCHAR(20) NOT NULL UNIQUE,
  `product_name` VARCHAR(150) NOT NULL,
  `description` TEXT,
  `product_image` VARCHAR(255),
  `current_quantity` INT NOT NULL DEFAULT 0,
  `unit` VARCHAR(30),
  `category` VARCHAR(100),
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 17. STOCK TRANSACTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `stock_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` VARCHAR(20) NOT NULL UNIQUE,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `transaction_type` ENUM('stock_added','stock_used','stock_adjustment','stock_returned') NOT NULL,
  `quantity` INT NOT NULL,
  `previous_quantity` INT NOT NULL,
  `new_quantity` INT NOT NULL,
  `reference_type` ENUM('project','beneficiary','manual') DEFAULT 'manual',
  `reference_id` BIGINT UNSIGNED,
  `description` TEXT,
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 18. BENEFICIARIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `beneficiaries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `beneficiary_id` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `guardian_name` VARCHAR(100),
  `guardian_relation` ENUM('father','mother','spouse','other') DEFAULT 'father',
  `dob` DATE,
  `gender` ENUM('male','female','other'),
  `mobile` VARCHAR(15),
  `address` TEXT,
  `city` VARCHAR(100),
  `state` VARCHAR(100),
  `pincode` VARCHAR(10),
  `aadhaar_number` VARCHAR(12),
  `category` VARCHAR(100),
  `project_id` BIGINT UNSIGNED,
  `assistance_type` VARCHAR(100),
  `status` ENUM('active','inactive','completed') NOT NULL DEFAULT 'active',
  `documents` VARCHAR(255),
  `photo` VARCHAR(255),
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 19. BENEFICIARY ASSISTANCE
-- ============================================================
CREATE TABLE IF NOT EXISTS `beneficiary_assistance` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `assistance_id` VARCHAR(20) NOT NULL UNIQUE,
  `beneficiary_id` BIGINT UNSIGNED NOT NULL,
  `project_id` BIGINT UNSIGNED,
  `assistance_type` ENUM('cash','product','service','scholarship','medical','food','other') NOT NULL,
  `product_id` BIGINT UNSIGNED,
  `quantity` INT,
  `amount` DECIMAL(10,2),
  `assistance_date` DATE NOT NULL,
  `description` TEXT,
  `supporting_document` VARCHAR(255),
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`beneficiary_id`) REFERENCES `beneficiaries`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 20. VOLUNTEERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `volunteers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `volunteer_id` VARCHAR(20) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `mobile` VARCHAR(15) NOT NULL,
  `email` VARCHAR(150),
  `address` TEXT,
  `skills` TEXT,
  `availability` ENUM('full_time','part_time','weekends','on_call') NOT NULL DEFAULT 'part_time',
  `department_id` BIGINT UNSIGNED,
  `assigned_project_id` BIGINT UNSIGNED,
  `joining_date` DATE,
  `status` ENUM('active','inactive','assigned','completed') NOT NULL DEFAULT 'active',
  `photo` VARCHAR(255),
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 21. NOTICES
-- ============================================================
CREATE TABLE IF NOT EXISTS `notices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NOT NULL,
  `flash_start_date` DATE,
  `flash_end_date` DATE,
  `status` ENUM('active','inactive','expired') NOT NULL DEFAULT 'active',
  `flash_to` ENUM('all','department','specific_user','role') NOT NULL DEFAULT 'all',
  `target_department_id` BIGINT UNSIGNED,
  `target_role_id` BIGINT UNSIGNED,
  `target_user_id` BIGINT UNSIGNED,
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`target_department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`target_role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`target_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 22. DOCUMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `file_path` VARCHAR(255) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(50),
  `file_size` INT,
  `category` VARCHAR(100),
  `reference_type` VARCHAR(50),
  `reference_id` BIGINT UNSIGNED,
  `is_public` TINYINT(1) NOT NULL DEFAULT 0,
  `uploaded_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 23. NOTIFICATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('donation','payment_failed','csr_company','csr_project','project_approval','expense','user_registration','notice','subscription','project_expiring','budget_exceeded','general') NOT NULL DEFAULT 'general',
  `reference_type` VARCHAR(50),
  `reference_id` BIGINT UNSIGNED,
  `target_user_id` BIGINT UNSIGNED,
  `target_role` VARCHAR(50),
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `read_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`target_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 24. AUDIT LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED,
  `user_name` VARCHAR(100),
  `action` VARCHAR(50) NOT NULL,
  `module` VARCHAR(100) NOT NULL,
  `record_id` BIGINT UNSIGNED,
  `old_values` JSON,
  `new_values` JSON,
  `description` TEXT,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 25. CONTACT MESSAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `mobile` VARCHAR(15),
  `subject` VARCHAR(200),
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `read_by` BIGINT UNSIGNED,
  `read_at` TIMESTAMP NULL,
  `replied` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`read_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 26. SETTINGS / NGO INFO
-- ============================================================
CREATE TABLE IF NOT EXISTS `settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(100) NOT NULL UNIQUE,
  `value` TEXT,
  `type` ENUM('text','textarea','file','boolean','json') NOT NULL DEFAULT 'text',
  `group` VARCHAR(50) NOT NULL DEFAULT 'general',
  `label` VARCHAR(150),
  `updated_by` BIGINT UNSIGNED,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 27. CMS PAGES / SLIDERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `cms_sliders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200),
  `subtitle` VARCHAR(300),
  `description` TEXT,
  `image` VARCHAR(255),
  `cta_text` VARCHAR(100),
  `cta_link` VARCHAR(255),
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ============================================================
-- 28. GALLERY
-- ============================================================
CREATE TABLE IF NOT EXISTS `gallery` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `media_file` VARCHAR(255) NOT NULL,
  `media_type` ENUM('image','video') NOT NULL DEFAULT 'image',
  `category` VARCHAR(100),
  `media_date` DATE,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `uploaded_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 29. GENERAL EXPENSES (Admin/Campaign expenses not tied to CSR)
-- ============================================================
CREATE TABLE IF NOT EXISTS `general_expenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `expense_id` VARCHAR(20) NOT NULL UNIQUE,
  `expense_type` ENUM('administration','campaign','program','beneficiary','other') NOT NULL,
  `expense_date` DATE NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(100),
  `payment_mode` ENUM('cash','cheque','bank_transfer','upi','card','other') NOT NULL DEFAULT 'cash',
  `vendor_name` VARCHAR(150),
  `bill_number` VARCHAR(100),
  `bill_file` VARCHAR(255),
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- DONE: All tables created
-- ============================================================