-- ============================================================
-- NGO MIS — COMPLETE DATABASE
-- Run this single file to set up everything
-- MySQL 8.0+ | UTF8MB4
-- ============================================================

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

-- Create and use database
-- CREATE DATABASE IF NOT EXISTS `ngo_mis` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `ngo_mis`;

-- ============================================================
-- 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
-- ============================================================
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. SUBSCRIPTIONS
-- ============================================================
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
-- ============================================================
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. 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
-- ============================================================
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 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
-- ============================================================
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;

-- ============================================================
-- ============================================================
--  SEEDERS — Default data
-- ============================================================
-- ============================================================

-- ROLES
INSERT INTO `roles` (`name`, `slug`, `description`, `is_system`) VALUES
('Super Admin', 'super_admin', 'Full access to all modules', 1),
('Admin', 'admin', 'Administrative access', 1),
('Manager', 'manager', 'Management and reporting access', 1),
('CSR Manager', 'csr_manager', 'CSR company, project and expense access', 1),
('Accountant', 'accountant', 'Donations and expenses access', 1),
('Volunteer', 'volunteer', 'Limited volunteer functions', 1),
('Staff', 'staff', 'Assigned modules only', 1);

-- PERMISSIONS
INSERT INTO `permissions` (`module`, `action`, `slug`, `description`) VALUES
('dashboard','view','dashboard.view','View dashboard'),
('users','view','users.view','View users'),
('users','create','users.create','Create users'),
('users','edit','users.edit','Edit users'),
('users','delete','users.delete','Delete users'),
('users','manage_permissions','users.manage_permissions','Manage user permissions'),
('departments','view','departments.view','View departments'),
('departments','create','departments.create','Create departments'),
('departments','edit','departments.edit','Edit departments'),
('departments','delete','departments.delete','Delete departments'),
('csr_companies','view','csr_companies.view','View CSR companies'),
('csr_companies','create','csr_companies.create','Create CSR companies'),
('csr_companies','edit','csr_companies.edit','Edit CSR companies'),
('csr_companies','delete','csr_companies.delete','Delete CSR companies'),
('project_categories','view','project_categories.view','View project categories'),
('project_categories','create','project_categories.create','Create project categories'),
('project_categories','edit','project_categories.edit','Edit project categories'),
('project_categories','delete','project_categories.delete','Delete project categories'),
('projects','view','projects.view','View projects'),
('projects','create','projects.create','Create projects'),
('projects','edit','projects.edit','Edit projects'),
('projects','delete','projects.delete','Delete projects'),
('projects','approve','projects.approve','Approve projects'),
('project_expenses','view','project_expenses.view','View project expenses'),
('project_expenses','create','project_expenses.create','Create project expenses'),
('project_expenses','edit','project_expenses.edit','Edit project expenses'),
('project_expenses','delete','project_expenses.delete','Delete project expenses'),
('project_expenses','override_budget','project_expenses.override_budget','Override budget limit'),
('donations','view','donations.view','View donations'),
('donations','create','donations.create','Create/record donations'),
('donations','edit','donations.edit','Edit donations'),
('donations','delete','donations.delete','Delete donations'),
('donations','generate_receipt','donations.generate_receipt','Generate donation receipts'),
('donors','view','donors.view','View donors'),
('donors','create','donors.create','Create donors'),
('donors','edit','donors.edit','Edit donors'),
('donors','delete','donors.delete','Delete donors'),
('products','view','products.view','View products'),
('products','create','products.create','Create products'),
('products','edit','products.edit','Edit products'),
('products','delete','products.delete','Delete products'),
('products','add_stock','products.add_stock','Add stock to products'),
('beneficiaries','view','beneficiaries.view','View beneficiaries'),
('beneficiaries','create','beneficiaries.create','Create beneficiaries'),
('beneficiaries','edit','beneficiaries.edit','Edit beneficiaries'),
('beneficiaries','delete','beneficiaries.delete','Delete beneficiaries'),
('volunteers','view','volunteers.view','View volunteers'),
('volunteers','create','volunteers.create','Create volunteers'),
('volunteers','edit','volunteers.edit','Edit volunteers'),
('volunteers','delete','volunteers.delete','Delete volunteers'),
('notices','view','notices.view','View notices'),
('notices','create','notices.create','Create notices'),
('notices','edit','notices.edit','Edit notices'),
('notices','delete','notices.delete','Delete notices'),
('reports','view','reports.view','View reports'),
('reports','export','reports.export','Export reports'),
('settings','view','settings.view','View settings'),
('settings','edit','settings.edit','Edit settings'),
('audit_logs','view','audit_logs.view','View audit logs'),
('gallery','view','gallery.view','View gallery'),
('gallery','create','gallery.create','Upload to gallery'),
('gallery','delete','gallery.delete','Delete gallery items'),
('general_expenses','view','general_expenses.view','View general expenses'),
('general_expenses','create','general_expenses.create','Create general expenses'),
('general_expenses','edit','general_expenses.edit','Edit general expenses'),
('general_expenses','delete','general_expenses.delete','Delete general expenses');

-- SUPER ADMIN gets ALL permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;

-- ADMIN - all
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, id FROM `permissions` WHERE slug != 'audit_logs.view';

-- MANAGER - view only
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 3, id FROM `permissions` WHERE action = 'view';

-- CSR MANAGER
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 4, id FROM `permissions` WHERE module IN ('csr_companies','projects','project_expenses','project_categories','dashboard') OR slug = 'reports.view';

-- ACCOUNTANT
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 5, id FROM `permissions` WHERE module IN ('donations','donors','general_expenses','reports','dashboard') OR action = 'view';

-- VOLUNTEER
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 6, id FROM `permissions` WHERE slug IN ('dashboard.view','volunteers.view','projects.view','notices.view');

-- STAFF
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 7, id FROM `permissions` WHERE slug IN ('dashboard.view','notices.view');

-- DEPARTMENTS
INSERT INTO `departments` (`name`, `description`, `status`) VALUES
('Administration', 'Administrative department', 'active'),
('Finance & Accounts', 'Finance and accounting department', 'active'),
('CSR & Projects', 'CSR management and project execution', 'active'),
('Volunteer Coordination', 'Volunteer management and coordination', 'active'),
('Field Operations', 'Ground level field operations', 'active'),
('Communications', 'PR and communications', 'active');

-- ============================================================
-- DEFAULT ADMIN USER
-- Email:    admin@ngomis.com
-- Password: Admin@123
-- ============================================================
INSERT INTO `users` (`user_id`, `name`, `email`, `mobile`, `password`, `designation`, `department_id`, `role_id`, `user_type`, `status`) VALUES
('USR-0001', 'Super Admin', 'admin@ngomis.com', '9999999999',
'$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
'System Administrator', 1, 1, 'super_admin', 'active');

-- PROJECT CATEGORIES
INSERT INTO `project_categories` (`name`, `slug`, `description`, `status`) VALUES
('Health', 'health', 'Healthcare and medical assistance projects', 'active'),
('Health & Sanitation', 'health-sanitation', 'Health and sanitation improvement projects', 'active'),
('Education', 'education', 'Educational support and scholarship projects', 'active'),
('Environment', 'environment', 'Environmental conservation projects', 'active'),
('Women Welfare', 'women-welfare', 'Women empowerment and welfare projects', 'active'),
('Child Welfare', 'child-welfare', 'Child development and welfare projects', 'active'),
('Skill Development', 'skill-development', 'Vocational training and skill development', 'active'),
('Livelihood', 'livelihood', 'Livelihood and income generation programs', 'active'),
('Disaster Relief', 'disaster-relief', 'Emergency and disaster relief operations', 'active');

-- DEFAULT SETTINGS
INSERT INTO `settings` (`key`, `value`, `type`, `group`, `label`) VALUES
('ngo_name', 'Helping Hands NGO', 'text', 'general', 'NGO Name'),
('ngo_tagline', 'Empowering Communities, Changing Lives', 'text', 'general', 'NGO Tagline'),
('ngo_registration_number', 'REG/2024/001', 'text', 'general', 'Registration Number'),
('ngo_email', 'info@helpinghands.org', 'text', 'general', 'Email'),
('ngo_phone', '+91 98765 43210', 'text', 'general', 'Phone'),
('ngo_address', '123, NGO Complex, New Delhi - 110001', 'textarea', 'general', 'Address'),
('ngo_website', 'https://www.helpinghands.org', 'text', 'general', 'Website URL'),
('ngo_facebook', '', 'text', 'social', 'Facebook URL'),
('ngo_twitter', '', 'text', 'social', 'Twitter URL'),
('ngo_instagram', '', 'text', 'social', 'Instagram URL'),
('ngo_youtube', '', 'text', 'social', 'YouTube URL'),
('ngo_logo', '', 'file', 'general', 'NGO Logo'),
('ngo_80g_registration', '', 'text', 'legal', '80G Registration Number'),
('ngo_12a_registration', '', 'text', 'legal', '12A Registration Number'),
('ngo_pan_number', '', 'text', 'legal', 'NGO PAN Number'),
('receipt_prefix', 'RCP', 'text', 'donation', 'Receipt Number Prefix'),
('donation_id_prefix', 'DON', 'text', 'donation', 'Donation ID Prefix'),
('razorpay_key_id', '', 'text', 'payment', 'Razorpay Key ID'),
('razorpay_key_secret', '', 'text', 'payment', 'Razorpay Key Secret'),
('payment_gateway', 'razorpay', 'text', 'payment', 'Active Payment Gateway'),
('email_host', '', 'text', 'email', 'SMTP Host'),
('email_port', '587', 'text', 'email', 'SMTP Port'),
('email_username', '', 'text', 'email', 'SMTP Username'),
('email_password', '', 'text', 'email', 'SMTP Password'),
('email_from_name', 'Helping Hands NGO', 'text', 'email', 'From Name'),
('email_from_address', 'noreply@helpinghands.org', 'text', 'email', 'From Email'),
('donation_receipt_footer', 'This receipt is computer generated and does not require signature.', 'textarea', 'donation', 'Receipt Footer Text'),
('homepage_hero_title', 'Together We Can Change Lives', 'text', 'cms', 'Hero Title'),
('homepage_hero_subtitle', 'Join us in our mission to empower communities and create lasting impact.', 'textarea', 'cms', 'Hero Subtitle'),
('homepage_about', '', 'textarea', 'cms', 'About Section Content'),
('map_embed_url', '', 'text', 'contact', 'Google Maps Embed URL');

-- ============================================================
-- END OF NGO_MIS_COMPLETE.SQL
-- ============================================================