-- Combined All SQL for KSO Chandigarh Database
-- Generated: 2025-07-12

-- Mode and transaction settings
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;

-- Include base schema and seed data
SOURCE ktxmmosu_kso_chandigarh.sql;

-- Drop tables from old photo system if present
SOURCE drop_photo_system_safe.sql;

-- Enhanced photo system migrations
SOURCE enhanced_photo_system.sql;
SOURCE enhanced_photo_system_migration.sql;
SOURCE enhanced_photo_system_duplicate_fix.sql;

-- Fix duplicate keys
SOURCE fix_duplicate_keys_migration.sql;

-- Email system setup and migrations
SOURCE email_setup.sql;
SOURCE email_system_migration.sql;
SOURCE email_setup_fixed.sql;

-- Receipt and invoice setup
SOURCE receipt_invoice_setup.sql;
SOURCE receipt_invoice_setup_fixed.sql;

-- Re-enable foreign key checks and commit
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

-- Begin drop_photo_system_safe.sql

-- Safe Photo System Table Drop Script
-- Run this first if you're having foreign key constraint issues

-- Disable foreign key checks completely
SET FOREIGN_KEY_CHECKS = 0;

-- Remove any existing foreign key constraints that might reference member_photos
SET @sql = (SELECT GROUP_CONCAT(CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name, ';') SEPARATOR ' ')
           FROM information_schema.referential_constraints 
           WHERE constraint_schema = DATABASE() 
           AND referenced_table_name = 'member_photos');

-- Execute the drop statements if any exist
SET @sql = IFNULL(@sql, 'SELECT "No foreign key constraints found for member_photos" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Now safely drop all enhanced photo system tables
DROP TABLE IF EXISTS `photo_upload_log`;
DROP TABLE IF EXISTS `member_activity_log`;
DROP TABLE IF EXISTS `photo_upload_sessions`;
DROP TABLE IF EXISTS `member_online_status`;
DROP TABLE IF EXISTS `member_photos`;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- Show success message
SELECT 'All enhanced photo system tables dropped successfully!' as Status;
SELECT 'You can now run the enhanced_photo_system.sql or enhanced_photo_system_migration.sql file' as NextStep;

-- Begin ktxmmosu_kso_chandigarh.sql

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Dec 15, 2024 at 10:00 AM
-- Server version: 10.11.11-MariaDB-cll-lve
-- PHP Version: 8.3.11

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

-- Disable foreign key checks to avoid constraint errors during table creation
SET FOREIGN_KEY_CHECKS = 0;

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

--
-- Table structure for table `activity_logs`
--

DROP TABLE IF EXISTS `activity_logs`;
CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `severity` enum('INFO','WARNING','ERROR','CRITICAL') DEFAULT 'INFO',
  `user_agent` text DEFAULT NULL,
  `session_id` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kso_id` varchar(20) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('President','Vice President','General Secretary','Finance Secretary','Assistant General Secretary','Secretary (Information & Publicity)','Secretary (Cultural)','Secretary (Education)','Secretary (Games & Sports)') NOT NULL,
  `term_from_year` int(11) NOT NULL,
  `term_to_year` int(11) NOT NULL,
  `status` enum('active','inactive','pending','expired') DEFAULT 'active',
  `phone` varchar(20) DEFAULT NULL,
  `college` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `year_of_study` int(11) DEFAULT NULL,
  `photo_url` varchar(500) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `password_reset_token` varchar(255) DEFAULT NULL,
  `password_reset_expires` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `kso_id` (`kso_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `members`
--

DROP TABLE IF EXISTS `members`;
CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kso_id` varchar(20) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email_token` varchar(64) DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT 0,
  `phone` varchar(20) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `blood_group` enum('A+','A-','B+','B-','AB+','AB-','O+','O-') DEFAULT NULL,
  `college` varchar(255) NOT NULL,
  `course` varchar(255) NOT NULL,
  `year_of_study` int(11) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `emergency_contact_name` varchar(255) DEFAULT NULL,
  `emergency_contact` varchar(20) DEFAULT NULL,
  `membership_type` enum('Individual','Family') NOT NULL,
  `status` enum('active','inactive','pending','suspended','expired','approved','rejected','under_review') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `assignment_notes` text DEFAULT NULL,
  `assignment_date` datetime DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `session_from_year` int(4) DEFAULT NULL,
  `session_to_year` int(4) DEFAULT NULL,
  `photo_url` varchar(500) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `membership_expiry_date` date DEFAULT NULL,
  `last_reminder_sent_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `kso_id` (`kso_id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email_token` (`email_token`),
  KEY `idx_email_verified` (`email_verified`),
  KEY `idx_approved_by` (`approved_by`),
  KEY `idx_assigned_to` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Create sequence table for KSO IDs
DROP TABLE IF EXISTS `kso_id_sequence`;
CREATE TABLE `kso_id_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Trigger to auto-generate KSO ID
DELIMITER $$
DROP TRIGGER IF EXISTS `generate_kso_id`$$
CREATE TRIGGER `generate_kso_id` BEFORE INSERT ON `members`
FOR EACH ROW
BEGIN
    IF NEW.kso_id IS NULL OR NEW.kso_id = '' THEN
        INSERT INTO kso_id_sequence () VALUES ();
        SET NEW.kso_id = CONCAT('KSOCHD', LPAD(LAST_INSERT_ID(), 3, '0'));
    END IF;
END$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `dependents`
--

DROP TABLE IF EXISTS `dependents`;
CREATE TABLE `dependents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `kso_id` varchar(20) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `relationship` enum('Spouse','Child','Sibling','Parent','Other') NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `college` varchar(255) DEFAULT NULL,
  `year_of_study` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `kso_id` (`kso_id`),
  KEY `member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Trigger to auto-generate KSO ID for dependents
DELIMITER $$
DROP TRIGGER IF EXISTS `generate_dependent_kso_id`$$
CREATE TRIGGER `generate_dependent_kso_id` BEFORE INSERT ON `dependents`
FOR EACH ROW
BEGIN
    IF NEW.kso_id IS NULL OR NEW.kso_id = '' THEN
        INSERT INTO kso_id_sequence () VALUES ();
        SET NEW.kso_id = CONCAT('KSOCHD', LPAD(LAST_INSERT_ID(), 3, '0'));
    END IF;
END$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_title` varchar(255) NOT NULL,
  `event_description` text DEFAULT NULL,
  `event_banner_url` varchar(500) DEFAULT NULL, -- Added this line
  `event_date` date NOT NULL,
  `event_time` time DEFAULT NULL,
  `event_location` varchar(255) DEFAULT NULL,
  `event_type` enum('meeting','cultural','sports','educational','social','other') NOT NULL,
  `max_participants` int(11) DEFAULT NULL,
  `registration_fee` decimal(10,2) DEFAULT 0.00,
  `status` enum('upcoming','ongoing','completed','cancelled') DEFAULT 'upcoming',
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_registrations`
--

DROP TABLE IF EXISTS `event_registrations`;
CREATE TABLE `event_registrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `registration_date` datetime DEFAULT current_timestamp(),
  `payment_status` enum('pending','paid','waived') DEFAULT 'pending',
  `attendance_status` enum('registered','attended','no_show','cancelled') DEFAULT 'registered',
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `finance`
--

DROP TABLE IF EXISTS `finance`;
CREATE TABLE `finance` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `member_id` int(11) NOT NULL,
  `transaction_type` enum('membership_fee','donation','event_fee','other') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` enum('cash','bank_transfer','mobile_money','card','cheque') NOT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `session_year` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending',
  `payment_date` date NOT NULL,
  `processed_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `expenses`
--

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_title` varchar(255) NOT NULL,
  `expense_category` enum('office_supplies','events','travel','utilities','maintenance','other') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `expense_date` date NOT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `receipt_file` varchar(500) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `status` enum('pending','approved','rejected','paid') DEFAULT 'pending',
  `payment_method` enum('cash','bank_transfer','cheque') NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `membership_fee_settings`
--

DROP TABLE IF EXISTS `membership_fee_settings`;
CREATE TABLE `membership_fee_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `membership_type` enum('Individual','Family') NOT NULL,
  `session_year` int(11) NOT NULL,
  `fee_amount` decimal(10,2) NOT NULL,
  `late_fee_amount` decimal(10,2) DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `documents`
--

DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `category` enum('general','financial','membership','events','policies','other') NOT NULL,
  `uploaded_by` int(11) NOT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `download_count` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `id_cards`
--

DROP TABLE IF EXISTS `id_cards`;
CREATE TABLE `id_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `card_number` varchar(50) NOT NULL,
  `card_type` enum('individual','family_head','dependent') NOT NULL,
  `issue_date` date NOT NULL,
  `expiry_date` date NOT NULL,
  `status` enum('active','expired','cancelled','replaced') DEFAULT 'active',
  `pdf_file` varchar(500) DEFAULT NULL,
  `qr_code` varchar(500) DEFAULT NULL,
  `generated_by` int(11) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `receipts`
--

DROP TABLE IF EXISTS `receipts`;
CREATE TABLE `receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `finance_id` int(11) NOT NULL,
  `receipt_number` varchar(50) NOT NULL,
  `receipt_type` enum('payment','expense','donation') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `pdf_file` varchar(500) DEFAULT NULL,
  `generated_by` int(11) NOT NULL,
  `generated_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
-- Note: Drop child tables first to avoid foreign key constraint errors
--

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `notification_recipients`;
DROP TABLE IF EXISTS `notifications`;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('info','success','warning','error','announcement','event','payment','membership') NOT NULL DEFAULT 'info',
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `target_audience` enum('all','members','admins','specific') NOT NULL DEFAULT 'all',
  `target_user_id` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `read_by` int(11) DEFAULT NULL,
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `expires_at` timestamp NULL DEFAULT NULL,
  `related_entity_type` varchar(50) DEFAULT NULL,
  `related_entity_id` int(11) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_target_audience` (`target_audience`),
  KEY `idx_target_user_id` (`target_user_id`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_priority` (`priority`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_notifications_created_target` (`created_at`, `target_audience`),
  KEY `idx_notifications_active` (`expires_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notification_recipients`
-- Note: This table is already dropped above with notifications table
--

CREATE TABLE `notification_recipients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notification_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_type` enum('admin','member') NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_notification_user` (`notification_id`,`user_id`,`user_type`),
  KEY `idx_notification_id` (`notification_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_type` (`user_type`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_recipients_user_read` (`user_id`, `user_type`, `is_read`),
  FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notification_settings`
--

DROP TABLE IF EXISTS `notification_settings`;
CREATE TABLE `notification_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('admin','member') NOT NULL,
  `browser_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `sound_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `email_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `push_notifications` tinyint(1) NOT NULL DEFAULT 0,
  `notification_types` json DEFAULT NULL,
  `quiet_hours_start` time DEFAULT NULL,
  `quiet_hours_end` time DEFAULT NULL,
  `timezone` varchar(50) DEFAULT 'Asia/Kolkata',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_settings` (`user_id`,`user_type`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('string','integer','boolean','json','file') DEFAULT 'string',
  `description` text DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
)  PRIMARY KEY (`id`),
)  UNIQUE KEY `unique_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `approval_logs`
--


DROP TABLE IF EXISTS `approval_logs`;
CREATE TABLE `approval_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `approved_by` int(11) NOT NULL,
  `upi_tx_id` varchar(100) DEFAULT NULL,
  `approved_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `ip_address` varchar(45) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `member_approval_history`
--

DROP TABLE IF EXISTS `member_approval_history`;
CREATE TABLE `member_approval_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `action` enum('submitted','assigned','approved','rejected','reassigned','updated') NOT NULL,
  `performed_by` int(11) NOT NULL,
  `from_status` enum('pending','approved','rejected','under_review') DEFAULT NULL,
  `to_status` enum('pending','approved','rejected','under_review') DEFAULT NULL,
  `assigned_from` int(11) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_performed_by` (`performed_by`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_approval_history_member_date` (`member_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admin_assignments`
--

DROP TABLE IF EXISTS `admin_assignments`;
CREATE TABLE `admin_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `assigned_to` int(11) NOT NULL,
  `assigned_by` int(11) NOT NULL,
  `assignment_type` enum('review','approval','verification','follow_up') NOT NULL DEFAULT 'review',
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `due_date` datetime DEFAULT NULL,
  `status` enum('active','completed','cancelled','overdue') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_assigned_by` (`assigned_by`),
  KEY `idx_assignment_type` (`assignment_type`),
  KEY `idx_priority` (`priority`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_assignments_active` (`assigned_to`, `status`, `due_date`),
  KEY `idx_assignments_workload` (`assigned_to`, `status`, `priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admin_roles`
--

DROP TABLE IF EXISTS `admin_roles`;
CREATE TABLE `admin_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(100) NOT NULL UNIQUE,
  `role_description` text DEFAULT NULL,
  `role_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Insert default roles and descriptions
INSERT INTO `admin_roles` (`role`, `role_description`, `role_level`) VALUES
('President', 'The chief executive officer who leads the organization with vision and strategic direction, overseeing all operations and representing the organization at the highest level.', 9),
('Vice President', 'The second-in-command who supports the President and steps in when needed, playing a crucial role in decision-making and organizational leadership.', 8),
('General Secretary', 'Responsible for administrative coordination, meeting management, and ensuring smooth communication between all departments and committee members.', 7),
('Finance Secretary', 'Manages the organization\'s financial resources, budgeting, accounting, and ensures fiscal responsibility and transparency in all monetary matters.', 6),
('Assistant General Secretary', 'Supports the General Secretary in administrative duties and serves as a backup for coordination activities, ensuring continuity in operations.', 5),
('Secretary (Information & Publicity)', 'Handles all communication, media relations, and publicity efforts to promote the organization\'s activities and maintain public engagement.', 4),
('Secretary (Cultural)', 'Organizes and oversees cultural events, festivals, and artistic activities that celebrate and promote the organization\'s cultural heritage.', 4),
('Secretary (Education)', 'Focuses on educational initiatives, academic programs, and learning opportunities to enhance knowledge and skills within the organization.', 4),
('Secretary (Games & Sports)', 'Coordinates sports activities, tournaments, and recreational programs to promote physical fitness and team spirit among members.', 4);

-- --------------------------------------------------------

--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `kso_id`, `first_name`, `last_name`, `nickname`, `username`, `email`, `password_hash`, `role`, `term_from_year`, `term_to_year`, `status`, `phone`, `college`, `course`, `year_of_study`, `notes`, `permissions`, `last_login`, `login_attempts`, `locked_until`, `password_reset_token`, `password_reset_expires`, `created_at`, `updated_at`) VALUES
(1, 'KSOCHD001', 'Haominthang', '', 'Hao', 'haominthang', 'haominthang@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'President', 2024, 2025, 'active', '555-0100', 'Panjab University', 'N/A', 0, 'President', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 'KSOCHD002', 'Haolam', '', 'Lam', 'haolam', 'haolam@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Vice President', 2024, 2025, 'active', '555-0101', 'Chandigarh University', 'N/A', 0, 'Vice President', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(3, 'KSOCHD003', 'Kamgoulen', '', 'Len', 'kamgoulen', 'kamgoulen@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'General Secretary', 2024, 2025, 'active', '555-0102', 'Panjab University', 'N/A', 0, 'General Secretary', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(4, 'KSOCHD004', 'Alex', 'Mangboi', 'Alex', 'alex.mangboi', 'alex.mangboi@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Finance Secretary', 2024, 2025, 'active', '555-0103', 'Chandigarh University', 'N/A', 0, 'Finance Secretary', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(5, 'KSOCHD005', 'Lamneihoi', '', 'Neihoi', 'lamneihoi', 'lamneihoi@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Assistant General Secretary', 2024, 2025, 'active', '555-0104', 'Panjab University', 'N/A', 0, 'Assistant General Secretary', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(6, 'KSOCHD006', 'Lungouhen', '', 'Gouhen', 'lungouhen', 'lungouhen@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Secretary (Information & Publicity)', 2024, 2025, 'active', '555-0105', 'Chandigarh University', 'N/A', 0, 'Secretary (Information & Publicity)', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(7, 'KSOCHD007', 'Lhingboineng', '', 'Neng', 'lhingboineng', 'lhingboineng@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Secretary (Cultural)', 2024, 2025, 'active', '555-0106', 'Panjab University', 'N/A', 0, 'Secretary (Cultural)', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(8, 'KSOCHD008', 'Lhinghoithem', '', 'Them', 'lhinghoithem', 'lhinghoithem@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Secretary (Education)', 2024, 2025, 'active', '555-0107', 'Chandigarh University', 'N/A', 0, 'Secretary (Education)', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(9, 'KSOCHD009', 'Thangminlun', '', 'Lun', 'thangminlun', 'thangminlun@ksochandigarh.com', 'f925916e2754e5e03f75dd58a5733251', 'Secretary (Games & Sports)', 2024, 2025, 'active', '555-0108', 'Panjab University', 'N/A', 0, 'Secretary (Games & Sports)', NULL, NULL, 0, NULL, NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`id`, `kso_id`, `first_name`, `last_name`, `nickname`, `email`, `phone`, `date_of_birth`, `gender`, `blood_group`, `college`, `course`, `year_of_study`, `address`, `emergency_contact_name`, `emergency_contact`, `membership_type`, `status`, `approved_by`, `approved_at`, `session_from_year`, `session_to_year`, `photo_url`, `notes`, `membership_expiry_date`, `last_reminder_sent_at`, `created_at`, `updated_at`) VALUES
(1, 'KSOCHD101', 'Zara', 'Hussain', 'Zara', 'zara.hussain@example.com', '555-0123', '2000-05-15', 'Female', 'O+', 'Panjab University', 'Computer Science', 3, 'Sector 15, Chandigarh', 'Ahmed Hussain', '555-0124', 'Individual', 'active', 1, '2024-12-01 10:00:00', 2024, 2025, NULL, 'Active member since 2022', NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 'KSOCHD102', 'Bilal', 'Ahmed', 'Billy', 'bilal.ahmed@example.com', '555-0125', '1999-08-22', 'Male', 'A-', 'Chandigarh University', 'Electrical Engineering', 4, 'Sector 22, Chandigarh', 'Sara Ahmed', '555-0126', 'Individual', 'active', 2, '2024-12-05 11:00:00', 2024, 2025, NULL, 'New member', NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(3, 'KSOCHD103', 'Aisha', 'Khan', 'Aisha', 'aisha.khan@example.com', '555-0127', '2001-03-10', 'Female', 'B+', 'Panjab University', 'Psychology', 2, 'Sector 11, Chandigarh', 'Imran Khan', '555-0128', 'Family', 'active', 3, '2024-12-10 12:00:00', 2024, 2025, NULL, 'Family membership', NULL, NULL, '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `dependents`
--

INSERT INTO `dependents` (`id`, `member_id`, `kso_id`, `first_name`, `last_name`, `relationship`, `date_of_birth`, `gender`, `course`, `college`, `year_of_study`, `notes`, `created_at`, `updated_at`) VALUES
(1, 3, 'KSOCHD104', 'Sana', 'Khan', 'Sibling', '2003-07-15', 'Female', 'Commerce', 'Panjab University', 1, 'Younger sister', '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `event_title`, `event_description`, `event_date`, `event_time`, `event_location`, `event_type`, `max_participants`, `registration_fee`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'KSO Annual Cultural Festival', 'Annual cultural festival showcasing Kashmiri culture and traditions', '2024-12-25', '18:00:00', 'Sector 17 Plaza, Chandigarh', 'cultural', 200, 500.00, 'upcoming', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 'Monthly General Meeting', 'Regular monthly meeting to discuss organizational matters', '2024-12-20', '19:00:00', 'KSO Office, Sector 15', 'meeting', 50, 0.00, 'upcoming', 3, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(3, 'Kashmiri Food Festival', 'Traditional Kashmiri food festival and cooking competition', '2024-12-30', '16:00:00', 'Rose Garden, Chandigarh', 'social', 150, 300.00, 'upcoming', 2, '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `event_registrations`
--

INSERT INTO `event_registrations` (`id`, `event_id`, `member_id`, `registration_date`, `payment_status`, `attendance_status`, `notes`) VALUES
(1, 1, 1, '2024-12-15 10:00:00', 'paid', 'registered', 'Excited to participate'),
(2, 1, 2, '2024-12-15 10:00:00', 'pending', 'registered', 'Will pay at venue'),
(3, 2, 1, '2024-12-15 10:00:00', 'waived', 'registered', 'Executive member'),
(4, 2, 2, '2024-12-15 10:00:00', 'waived', 'registered', 'Regular member'),
(5, 3, 3, '2024-12-15 10:00:00', 'paid', 'registered', 'Bringing family');

-- --------------------------------------------------------

--
-- Dumping data for table `finance`
--

INSERT INTO `finance` (`id`, `member_id`, `transaction_type`, `amount`, `payment_method`, `receipt_number`, `session_year`, `description`, `status`, `payment_date`, `processed_by`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, 'membership_fee', 1000.00, 'cash', 'RCPT-001', 2024, 'Annual membership fee 2024-25', 'completed', '2024-12-01', 3, 'Paid in cash', '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 2, 'membership_fee', 1000.00, 'bank_transfer', 'RCPT-002', 2024, 'Annual membership fee 2024-25', 'completed', '2024-12-05', 3, 'Online payment', '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(3, 3, 'membership_fee', 1500.00, 'cash', 'RCPT-003', 2024, 'Family membership fee 2024-25', 'completed', '2024-12-10', 3, 'Family membership', '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(4, 1, 'event_fee', 500.00, 'cash', 'RCPT-004', 2024, 'Cultural festival registration', 'completed', '2024-12-15', 3, 'Event fee', '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(5, 3, 'event_fee', 300.00, 'cash', 'RCPT-005', 2024, 'Food festival registration', 'completed', '2024-12-15', 3, 'Event fee', '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `membership_fee_settings`
--

INSERT INTO `membership_fee_settings` (`id`, `membership_type`, `session_year`, `fee_amount`, `late_fee_amount`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Individual', 2024, 1000.00, 200.00, 'Annual individual membership fee for 2024-25', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 'Family', 2024, 1500.00, 300.00, 'Annual family membership fee for 2024-25', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
(1, 'organization_name', 'Kashmiri Students Organization (KSO) Chandigarh', 'string', 'Organization Name', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(2, 'organization_address', 'Sector 15, Chandigarh, India', 'string', 'Organization Address', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(3, 'organization_phone', '+91-555-0100', 'string', 'Organization Phone Number', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(4, 'organization_email', 'info@ksochandigarh.com', 'string', 'Organization Email', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(5, 'current_session_year', '2024', 'string', 'Current Academic Session Year', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(6, 'emergency_contact', '+91-555-9999', 'string', 'Emergency Contact Number', 1, '2024-12-15 10:00:00', '2024-12-15 10:00:00'),
(7, 'notification_system_enabled', '1', 'boolean', 'Enable/Disable Real-time Notification System', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(8, 'notification_sound_enabled', '1', 'boolean', 'Default Sound Notification Setting', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(9, 'notification_browser_enabled', '1', 'boolean', 'Default Browser Notification Setting', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(10, 'notification_cleanup_days', '30', 'integer', 'Auto-delete notifications older than N days', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(11, 'notification_max_per_user', '50', 'integer', 'Maximum notifications per user at any time', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(12, 'member_approval_enabled', '1', 'boolean', 'Enable/Disable Member Approval System', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(13, 'member_auto_assignment', '0', 'boolean', 'Automatically assign new members to admins', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(14, 'member_approval_timeout_days', '7', 'integer', 'Days before approval request times out', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(15, 'email_enabled', '1', 'boolean', 'Enable/Disable Email System', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(16, 'smtp_host', 'smtp.gmail.com', 'string', 'SMTP Server Host', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(17, 'smtp_port', '587', 'integer', 'SMTP Server Port', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(18, 'smtp_username', 'your-email@gmail.com', 'string', 'SMTP Username', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(19, 'smtp_password', 'your-app-password', 'string', 'SMTP Password', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(20, 'smtp_encryption', 'tls', 'string', 'SMTP Encryption (tls/ssl)', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(21, 'email_from_address', 'noreply@ksochandigarh.com', 'string', 'From Email Address', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(22, 'email_from_name', 'KSO Chandigarh', 'string', 'From Name', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00'),
(23, 'member_assignment_notification', '1', 'boolean', 'Send notifications for member assignments', 0, '2025-01-07 10:00:00', '2025-01-07 10:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `type`, `title`, `message`, `target_audience`, `created_by`, `priority`, `created_at`) VALUES
(1, 'announcement', 'Welcome to KSO CHD Management System', 'The new real-time notification system is now live! You will receive important updates, announcements, and event notifications through this system. Click the bell icon in the top bar to view your notifications.', 'all', 1, 'normal', '2025-01-07 10:00:00'),
(2, 'info', 'System Maintenance Notice', 'Scheduled maintenance will be performed on Sunday, January 12th from 2:00 AM to 4:00 AM IST. Services may be temporarily unavailable during this time. We apologize for any inconvenience.', 'all', 1, 'high', '2025-01-07 10:15:00'),
(3, 'success', 'Profile Updated Successfully', 'Your profile information has been updated successfully. All changes have been saved and are now active in the system.', 'members', 1, 'low', '2025-01-07 10:30:00'),
(4, 'event', 'KSO Annual Cultural Festival Registration Open', 'Registration is now open for the KSO Annual Cultural Festival. Limited seats available. Register now to secure your spot!', 'all', 1, 'high', '2025-01-07 11:00:00'),
(5, 'payment', 'Membership Fee Payment Reminder', 'Your annual membership fee payment is due. Please complete your payment to maintain active membership status.', 'members', 1, 'normal', '2025-01-07 11:30:00');

-- --------------------------------------------------------

--
-- Dumping data for table `notification_settings`
--

-- Insert default notification settings for existing admins
INSERT INTO `notification_settings` 
  (`user_id`, `user_type`, `browser_notifications`, `sound_notifications`, `email_notifications`, `notification_types`)
SELECT 
  `id`, 
  'admin',
  1,
  1,
  1,
  '["info", "success", "warning", "error", "announcement", "event", "payment", "membership"]'
FROM `admins`;

-- Insert default notification settings for existing members
INSERT INTO `notification_settings` 
  (`user_id`, `user_type`, `browser_notifications`, `sound_notifications`, `email_notifications`, `notification_types`)
SELECT 
  `id`, 
  'member',
  1,
  1,
  1,
  '["info", "success", "warning", "error", "announcement", "event", "payment", "membership"]'
FROM `members`;

-- --------------------------------------------------------

--
-- Dumping data for table `notification_recipients`
--

-- Create notification recipients for the sample notifications
-- All users get the announcement and system maintenance notifications
INSERT INTO `notification_recipients` (`notification_id`, `user_id`, `user_type`)
SELECT 1, `id`, 'admin' FROM `admins`
UNION ALL
SELECT 1, `id`, 'member' FROM `members`
UNION ALL
SELECT 2, `id`, 'admin' FROM `admins`
UNION ALL
SELECT 2, `id`, 'member' FROM `members`
UNION ALL
SELECT 4, `id`, 'admin' FROM `admins`
UNION ALL
SELECT 4, `id`, 'member' FROM `members`;

-- Members only get the profile and payment notifications
INSERT INTO `notification_recipients` (`notification_id`, `user_id`, `user_type`)
SELECT 3, `id`, 'member' FROM `members`
UNION ALL
SELECT 5, `id`, 'member' FROM `members`;

-- --------------------------------------------------------

--
-- Dumping data for table `member_approval_history`
--

INSERT INTO `member_approval_history` (`id`, `member_id`, `action`, `performed_by`, `from_status`, `to_status`, `notes`, `created_at`) VALUES
(1, 1, 'submitted', 1, NULL, 'pending', 'Initial application submitted', '2024-11-30 09:00:00'),
(2, 1, 'assigned', 1, 'pending', 'under_review', 'Assigned to admin for review', '2024-11-30 10:00:00'),
(3, 1, 'approved', 1, 'under_review', 'approved', 'Application approved after document verification', '2024-12-01 10:00:00'),
(4, 2, 'submitted', 2, NULL, 'pending', 'Initial application submitted', '2024-12-04 14:00:00'),
(5, 2, 'assigned', 2, 'pending', 'under_review', 'Assigned to admin for review', '2024-12-04 15:00:00'),
(6, 2, 'approved', 2, 'under_review', 'approved', 'Quick approval - all documents in order', '2024-12-05 11:00:00'),
(7, 3, 'submitted', 3, NULL, 'pending', 'Family membership application submitted', '2024-12-09 11:00:00'),
(8, 3, 'assigned', 3, 'pending', 'under_review', 'Family application assigned for review', '2024-12-09 12:00:00'),
(9, 3, 'approved', 3, 'under_review', 'approved', 'Family membership approved with dependent', '2024-12-10 12:00:00');

-- --------------------------------------------------------

--
-- Dumping data for table `admin_assignments`
--

INSERT INTO `admin_assignments` (`id`, `member_id`, `assigned_to`, `assigned_by`, `assignment_type`, `priority`, `status`, `notes`, `completed_at`, `created_at`) VALUES
(1, 1, 1, 1, 'review', 'normal', 'completed', 'Initial review and approval of membership application', '2024-12-01 10:00:00', '2024-11-30 10:00:00'),
(2, 2, 2, 2, 'review', 'normal', 'completed', 'Fast-track review for complete application', '2024-12-05 11:00:00', '2024-12-04 15:00:00'),
(3, 3, 3, 3, 'verification', 'high', 'completed', 'Family membership verification and approval', '2024-12-10 12:00:00', '2024-12-09 12:00:00');

-- --------------------------------------------------------

--
-- Stored Procedures
--

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_adminlogin`$$
CREATE PROCEDURE `sp_adminlogin`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    SELECT id, CONCAT(first_name, ' ', last_name) as FullName, nickname, username, email, role
    FROM admins 
    WHERE username = p_username 
    AND password_hash = p_password 
    AND status = 'active';
END$$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_adminpasswordrecovery`$$
CREATE PROCEDURE `sp_adminpasswordrecovery`(
    IN `admin_email` VARCHAR(255),
    IN `new_password` VARCHAR(255)
)
BEGIN
    DECLARE `hashed_password` VARCHAR(255);
    SET `hashed_password` = PASSWORD(new_password);
    UPDATE `admins`
    SET `password_hash` = `hashed_password`,
        `password_reset_token` = NULL,
        `password_reset_expires` = NULL
    WHERE `email` = `admin_email`;
END$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_entity` (`entity_type`,`entity_id`),
  ADD KEY `idx_severity` (`severity`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_ip_address` (`ip_address`),
  ADD KEY `idx_activity_logs_user_date` (`user_id`,`created_at`);

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_role` (`role`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_updated_at` (`updated_at`);

--
-- Indexes for table `members`
--
ALTER TABLE `members`
  ADD KEY `idx_kso_id` (`kso_id`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_membership_type` (`membership_type`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_college` (`college`),
  ADD KEY `idx_course` (`course`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_members_status_type` (`status`,`membership_type`),
  ADD KEY `idx_approved_by` (`approved_by`),
  ADD KEY `idx_assigned_to` (`assigned_to`);

--
-- Indexes for table `dependents`
--
ALTER TABLE `dependents`
  ADD KEY `idx_member_id` (`member_id`),
  ADD KEY `idx_relationship` (`relationship`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD KEY `idx_event_title` (`event_title`),
  ADD KEY `idx_event_date` (`event_date`),
  ADD KEY `idx_event_type` (`event_type`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_by` (`created_by`);

--
-- Indexes for table `event_registrations`
--
ALTER TABLE `event_registrations`
  ADD UNIQUE KEY `unique_registration` (`event_id`,`member_id`),
  ADD KEY `idx_event_id` (`event_id`),
  ADD KEY `idx_member_id` (`member_id`),
  ADD KEY `idx_registration_date` (`registration_date`),
  ADD KEY `idx_payment_status` (`payment_status`),
  ADD KEY `idx_attendance_status` (`attendance_status`);

--
-- Indexes for table `finance`
--
ALTER TABLE `finance`
  ADD UNIQUE KEY `receipt_number` (`receipt_number`),
  ADD KEY `idx_member_id` (`member_id`),
  ADD KEY `idx_transaction_type` (`transaction_type`),
  ADD KEY `idx_payment_method` (`payment_method`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_payment_date` (`payment_date`),
  ADD KEY `idx_session_year` (`session_year`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD KEY `idx_expense_title` (`expense_title`),
  ADD KEY `idx_expense_category` (`expense_category`),
  ADD KEY `idx_amount` (`amount`),
  ADD KEY `idx_expense_date` (`expense_date`),
  ADD KEY `idx_receipt_number` (`receipt_number`),
  ADD KEY `idx_receipt_file` (`receipt_file`),
  ADD KEY `idx_description` (`description`),
  ADD KEY `idx_approved_by` (`approved_by`),
  ADD KEY `idx_uploaded_by` (`uploaded_by`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_payment_method` (`payment_method`),
  ADD KEY `idx_notes` (`notes`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_updated_at` (`updated_at`);

--
-- Indexes for table `membership_fee_settings`
--
ALTER TABLE `membership_fee_settings`
  ADD KEY `idx_membership_type` (`membership_type`),
  ADD KEY `idx_session_year` (`session_year`),
  ADD KEY `idx_fee_amount` (`fee_amount`),
  ADD KEY `idx_late_fee_amount` (`late_fee_amount`),
  ADD KEY `idx_description` (`description`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_updated_at` (`updated_at`);

--
-- Indexes for table `documents`
--
ALTER TABLE `documents`
  ADD KEY `idx_title` (`title`),
  ADD KEY `idx_description` (`description`),
  ADD KEY `idx_file_name` (`file_name`),
  ADD KEY `idx_file_path` (`file_path`),
  ADD KEY `idx_file_size` (`file_size`),
  ADD KEY `idx_file_type` (`file_type`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_uploaded_by` (`uploaded_by`);

--
-- Constraints for table `expenses`
--
ALTER TABLE `expenses`
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- Constraints for table `expenses`
--
ALTER TABLE `expenses`
  ADD CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

--
-- Foreign Key Constraints for Member Approval System
--
ALTER TABLE `members`
  ADD CONSTRAINT `members_approved_by_fk` FOREIGN KEY (`approved_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `members_assigned_to_fk` FOREIGN KEY (`assigned_to`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `member_approval_history`
  ADD CONSTRAINT `approval_history_member_fk` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `approval_history_performed_by_fk` FOREIGN KEY (`performed_by`) REFERENCES `admins` (`id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `approval_history_assigned_from_fk` FOREIGN KEY (`assigned_from`) REFERENCES `admins` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `approval_history_assigned_to_fk` FOREIGN KEY (`assigned_to`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `admin_assignments`
  ADD CONSTRAINT `assignments_member_fk` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignments_assigned_to_fk` FOREIGN KEY (`assigned_to`) REFERENCES `admins` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignments_assigned_by_fk` FOREIGN KEY (`assigned_by`) REFERENCES `admins` (`id`) ON DELETE RESTRICT;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

COMMIT;

-- --------------------------------------------------------

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD UNIQUE KEY `setting_key` (`setting_key`),
  ADD KEY `idx_setting_type` (`setting_type`),
  ADD KEY `idx_is_public` (`is_public`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_updated_at` (`updated_at`);

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_logs`
--
ALTER TABLE `activity_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `members`
--
ALTER TABLE `members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `dependents`
--
ALTER TABLE `dependents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `event_registrations`
--
ALTER TABLE `event_registrations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `finance`
--
ALTER TABLE `finance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `documents`
--
ALTER TABLE `documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `membership_fee_settings`
--
ALTER TABLE `membership_fee_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `notification_logs`
--
ALTER TABLE `notification_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notification_settings`
--
ALTER TABLE `notification_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `approval_logs`
--
ALTER TABLE `approval_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `member_approval_history`
--
ALTER TABLE `member_approval_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin_assignments`
--
ALTER TABLE `admin_assignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin_roles`
--
ALTER TABLE `admin_roles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

-- --------------------------------------------------------

--
-- Table structure for table `renewal_settings`
--

CREATE TABLE `renewal_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(255) NOT NULL,
  `setting_value` varchar(255) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `renewal_settings`
--

INSERT INTO `renewal_settings` (`id`, `setting_key`, `setting_value`) VALUES
(1, 'reminder_interval_days', '30'),
(2, 'reminders_enabled', '1');

--
-- Indexes for dumped tables
--

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `renewal_settings`
--
ALTER TABLE `renewal_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;


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

-- Note: Member authentication fields (password_hash, email_token, email_verified) 
-- and their indexes are now included in the main CREATE TABLE statement above

-- Begin enhanced_photo_system.sql

-- Enhanced Photo System and Real-time Status Tracking
-- Database Schema for KSO Chandigarh
-- FIXED VERSION - Safe foreign key handling

-- ========================================
-- Pre-setup: Disable foreign key checks for safe migration
-- ========================================
SET FOREIGN_KEY_CHECKS = 0;

-- ========================================
-- Drop all existing constraints that might reference member_photos
-- ========================================

-- Drop foreign key constraints from members table if they exist
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.table_constraints 
                         WHERE table_schema = DATABASE() 
                         AND table_name = 'members' 
                         AND constraint_name = 'fk_members_photo');

SET @sql = IF(@constraint_exists > 0, 
    'ALTER TABLE members DROP FOREIGN KEY fk_members_photo;', 
    'SELECT "fk_members_photo constraint does not exist" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Drop all enhanced photo system tables in dependency order
-- (Dependencies first, then referenced tables)
DROP TABLE IF EXISTS `photo_upload_log`;
DROP TABLE IF EXISTS `member_activity_log`;
DROP TABLE IF EXISTS `photo_upload_sessions`;
DROP TABLE IF EXISTS `member_online_status`;
DROP TABLE IF EXISTS `member_photos`;

-- ========================================
-- Member Photos Table (Safe recreation)
-- ========================================
CREATE TABLE `member_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_size` bigint(20) NOT NULL,
  `upload_date` datetime DEFAULT current_timestamp(),
  `uploaded_by` int(11) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  `is_profile_photo` tinyint(1) DEFAULT 0,
  `dimensions_width` int(11) DEFAULT NULL,
  `dimensions_height` int(11) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `processing_status` enum('uploading','processing','completed','failed') DEFAULT 'uploading',
  `has_thumbnail` tinyint(1) DEFAULT 0,
  `has_optimized` tinyint(1) DEFAULT 0,
  `has_webp` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_uploaded_by` (`uploaded_by`),
  KEY `idx_profile_photo` (`member_id`, `is_profile_photo`),
  KEY `idx_upload_date` (`upload_date`),
  KEY `idx_processing_status` (`processing_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Member Online Status Table (Safe recreation)
-- ========================================
CREATE TABLE `member_online_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `is_online` tinyint(1) DEFAULT 0,
  `last_activity` datetime DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `last_logout` datetime DEFAULT NULL,
  `session_id` varchar(128) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `activity_type` enum('login','logout','page_view','api_call','idle','active') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_member_id` (`member_id`),
  KEY `idx_is_online` (`is_online`),
  KEY `idx_last_activity` (`last_activity`),
  KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Photo Upload Sessions (for chunked uploads)
-- ========================================
CREATE TABLE `photo_upload_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(128) NOT NULL,
  `member_id` int(11) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `original_filename` varchar(255) NOT NULL,
  `total_size` bigint(20) NOT NULL,
  `chunks_total` int(11) NOT NULL,
  `chunks_uploaded` int(11) DEFAULT 0,
  `upload_status` enum('in_progress','completed','failed','cancelled') DEFAULT 'in_progress',
  `file_hash` varchar(64) DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_session` (`session_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_uploaded_by` (`uploaded_by`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_upload_status` (`upload_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Photo Upload Activity Log
-- ========================================
CREATE TABLE `photo_upload_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(128) DEFAULT NULL,
  `photo_id` int(11) DEFAULT NULL,
  `action` enum('chunk_upload','file_assembly','optimization','thumbnail_creation','completion','error') NOT NULL,
  `details` json DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `processing_time_ms` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_photo_id` (`photo_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Real-time Activity Log
-- ========================================
CREATE TABLE `member_activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `activity_type` enum('login','logout','page_view','photo_upload','profile_update','password_change','other') NOT NULL,
  `activity_details` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `session_id` varchar(128) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_activity_type` (`activity_type`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Update Members Table for Photo Integration (Safe column addition)
-- ========================================

-- Add photo_id column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'photo_id');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN photo_id int(11) DEFAULT NULL AFTER photo_url;', 
    'SELECT "photo_id column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add last_activity_at column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'last_activity_at');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN last_activity_at datetime DEFAULT NULL AFTER updated_at;', 
    'SELECT "last_activity_at column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_online column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'is_online');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN is_online tinyint(1) DEFAULT 0 AFTER last_activity_at;', 
    'SELECT "is_online column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes if they don't exist
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_photo_id');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_photo_id (photo_id);', 
    'SELECT "idx_photo_id index already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_last_activity');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_last_activity (last_activity_at);', 
    'SELECT "idx_last_activity index already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_online_status');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_online_status (is_online);', 
    'SELECT "idx_online_status index already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Foreign Key Constraints (Added after table creation)
-- ========================================

-- Re-enable foreign key checks and add constraints safely
SET FOREIGN_KEY_CHECKS = 1;

ALTER TABLE `member_photos`
ADD CONSTRAINT `fk_member_photos_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_member_photos_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `member_online_status`
ADD CONSTRAINT `fk_online_status_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE;

ALTER TABLE `photo_upload_sessions`
ADD CONSTRAINT `fk_upload_session_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_upload_session_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `member_activity_log`
ADD CONSTRAINT `fk_activity_log_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE;

-- Note: Add members to photos foreign key constraint if it doesn't exist
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.table_constraints 
                         WHERE table_schema = DATABASE() 
                         AND table_name = 'members' 
                         AND constraint_name = 'fk_members_photo');

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE members ADD CONSTRAINT fk_members_photo FOREIGN KEY (photo_id) REFERENCES member_photos (id) ON DELETE SET NULL;', 
    'SELECT "fk_members_photo constraint already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Triggers for Online Status Management
-- ========================================

-- Trigger to update member online status on activity
DELIMITER $$
DROP TRIGGER IF EXISTS `update_member_activity`$$
CREATE TRIGGER `update_member_activity` AFTER INSERT ON `member_activity_log`
FOR EACH ROW
BEGIN
    -- Update member's last activity
    UPDATE `members` 
    SET 
        `last_activity_at` = NEW.created_at,
        `is_online` = 1
    WHERE `id` = NEW.member_id;
    
    -- Update or insert online status record
    INSERT INTO `member_online_status` 
        (`member_id`, `is_online`, `last_activity`, `session_id`, `ip_address`, `user_agent`, `activity_type`)
    VALUES 
        (NEW.member_id, 1, NEW.created_at, NEW.session_id, NEW.ip_address, NEW.user_agent, 
         CASE NEW.activity_type WHEN 'login' THEN 'login' ELSE 'active' END)
    ON DUPLICATE KEY UPDATE
        `is_online` = 1,
        `last_activity` = NEW.created_at,
        `session_id` = NEW.session_id,
        `ip_address` = NEW.ip_address,
        `user_agent` = NEW.user_agent,
        `activity_type` = CASE NEW.activity_type WHEN 'login' THEN 'login' ELSE 'active' END,
        `updated_at` = CURRENT_TIMESTAMP;
        
    -- Set last_login if this is a login activity
    IF NEW.activity_type = 'login' THEN
        UPDATE `member_online_status` 
        SET `last_login` = NEW.created_at 
        WHERE `member_id` = NEW.member_id;
    END IF;
END$$

-- Trigger to update photo association when member photo is set
DROP TRIGGER IF EXISTS `update_member_photo_association`$$
CREATE TRIGGER `update_member_photo_association` AFTER UPDATE ON `members`
FOR EACH ROW
BEGIN
    -- If photo_id changed, update the photo record
    IF OLD.photo_id != NEW.photo_id OR (OLD.photo_id IS NULL AND NEW.photo_id IS NOT NULL) THEN
        -- Unset previous profile photo
        IF OLD.photo_id IS NOT NULL THEN
            UPDATE `member_photos` 
            SET `is_profile_photo` = 0 
            WHERE `id` = OLD.photo_id;
        END IF;
        
        -- Set new profile photo
        IF NEW.photo_id IS NOT NULL THEN
            UPDATE `member_photos` 
            SET 
                `is_profile_photo` = 1,
                `member_id` = NEW.id
            WHERE `id` = NEW.photo_id;
        END IF;
    END IF;
END$$

DELIMITER ;

-- ========================================
-- Sample Data and Testing
-- ========================================

-- Insert sample online status for existing members
INSERT INTO `member_online_status` (`member_id`, `is_online`, `last_activity`)
SELECT `id`, 0, `updated_at` 
FROM `members` 
WHERE `id` NOT IN (SELECT `member_id` FROM `member_online_status`);

-- ========================================
-- Cleanup Procedures
-- ========================================

-- Procedure to mark members offline after inactivity
DELIMITER $$
DROP PROCEDURE IF EXISTS `cleanup_offline_members`$$
CREATE PROCEDURE `cleanup_offline_members`()
BEGIN
    -- Mark members offline if no activity for 5 minutes
    UPDATE `member_online_status` 
    SET 
        `is_online` = 0,
        `activity_type` = 'idle',
        `last_logout` = CURRENT_TIMESTAMP
    WHERE 
        `is_online` = 1 
        AND `last_activity` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE);
        
    -- Update members table as well
    UPDATE `members` 
    SET `is_online` = 0 
    WHERE 
        `is_online` = 1 
        AND `last_activity_at` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE);
END$$

-- Procedure to cleanup old upload sessions
DROP PROCEDURE IF EXISTS `cleanup_expired_upload_sessions`$$
CREATE PROCEDURE `cleanup_expired_upload_sessions`()
BEGIN
    -- Delete expired upload sessions and their chunks
    DELETE FROM `photo_upload_sessions` 
    WHERE `expires_at` < CURRENT_TIMESTAMP;
    
    -- Clean up temporary files older than 1 hour
    DELETE FROM `photo_upload_log` 
    WHERE `created_at` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR)
    AND `action` = 'chunk_upload';
END$$

DELIMITER ;

-- ========================================
-- Indexes for Performance (Safe creation)
-- ========================================

-- Additional indexes for better performance (with existence checks)

-- Index for members online activity
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_members_online_activity');

SET @sql = IF(@index_exists = 0, 
    'CREATE INDEX idx_members_online_activity ON members (is_online, last_activity_at);', 
    'SELECT "idx_members_online_activity already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for photo member profile
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'member_photos' 
                     AND index_name = 'idx_photo_member_profile');

SET @sql = IF(@index_exists = 0, 
    'CREATE INDEX idx_photo_member_profile ON member_photos (member_id, is_profile_photo, processing_status);', 
    'SELECT "idx_photo_member_profile already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for activity log recent
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'member_activity_log' 
                     AND index_name = 'idx_activity_log_recent');

SET @sql = IF(@index_exists = 0, 
    'CREATE INDEX idx_activity_log_recent ON member_activity_log (created_at DESC, member_id);', 
    'SELECT "idx_activity_log_recent already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for online status activity
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'member_online_status' 
                     AND index_name = 'idx_online_status_activity');

SET @sql = IF(@index_exists = 0, 
    'CREATE INDEX idx_online_status_activity ON member_online_status (last_activity, is_online);', 
    'SELECT "idx_online_status_activity already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Composite indexes for common queries

-- Index for members status activity (only if columns exist)
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_members_status_activity');

SET @approval_status_exists = (SELECT COUNT(*) FROM information_schema.columns 
                               WHERE table_schema = DATABASE() 
                               AND table_name = 'members' 
                               AND column_name = 'approval_status');

SET @status_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'status');

SET @last_activity_exists = (SELECT COUNT(*) FROM information_schema.columns 
                             WHERE table_schema = DATABASE() 
                             AND table_name = 'members' 
                             AND column_name = 'last_activity_at');

SET @sql = IF(@index_exists = 0 AND @approval_status_exists > 0 AND @status_exists > 0 AND @last_activity_exists > 0, 
    'CREATE INDEX idx_members_status_activity ON members (approval_status, status, last_activity_at);', 
    'SELECT "idx_members_status_activity skipped - required columns may not exist" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for photos status date
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'member_photos' 
                     AND index_name = 'idx_photos_status_date');

SET @sql = IF(@index_exists = 0, 
    'CREATE INDEX idx_photos_status_date ON member_photos (processing_status, upload_date);', 
    'SELECT "idx_photos_status_date already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Views for Easy Data Access
-- ========================================

-- View for member photos with status
CREATE OR REPLACE VIEW `v_member_photos` AS
SELECT 
    mp.*,
    m.first_name,
    m.last_name,
    m.email,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    CONCAT(a.first_name, ' ', a.last_name) as uploader_name
FROM `member_photos` mp
LEFT JOIN `members` m ON mp.member_id = m.id
LEFT JOIN `admins` a ON mp.uploaded_by = a.id;

-- View for online members with activity (only if columns exist)
SET @approval_status_exists = (SELECT COUNT(*) FROM information_schema.columns 
                               WHERE table_schema = DATABASE() 
                               AND table_name = 'members' 
                               AND column_name = 'approval_status');

SET @status_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'status');

SET @sql = IF(@approval_status_exists > 0 AND @status_exists > 0,
    'CREATE OR REPLACE VIEW v_members_online_status AS
    SELECT 
        m.id,
        m.kso_id,
        m.first_name,
        m.last_name,
        m.email,
        m.is_online,
        m.last_activity_at,
        mos.last_login,
        mos.last_logout,
        mos.session_id,
        mos.ip_address,
        CASE 
            WHEN m.is_online = 1 THEN "online"
            WHEN m.last_activity_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE) THEN "recently_active"
            WHEN m.last_activity_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR) THEN "away"
            ELSE "offline"
        END as status_label,
        TIMESTAMPDIFF(MINUTE, m.last_activity_at, CURRENT_TIMESTAMP) as minutes_since_activity
    FROM members m
    LEFT JOIN member_online_status mos ON m.id = mos.member_id
    WHERE m.approval_status = "approved" AND m.status = "active";',
    'CREATE OR REPLACE VIEW v_members_online_status AS
    SELECT 
        m.id,
        m.kso_id,
        m.first_name,
        m.last_name,
        m.email,
        m.is_online,
        m.last_activity_at,
        mos.last_login,
        mos.last_logout,
        mos.session_id,
        mos.ip_address,
        CASE 
            WHEN m.is_online = 1 THEN "online"
            WHEN m.last_activity_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE) THEN "recently_active"
            WHEN m.last_activity_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR) THEN "away"
            ELSE "offline"
        END as status_label,
        TIMESTAMPDIFF(MINUTE, m.last_activity_at, CURRENT_TIMESTAMP) as minutes_since_activity
    FROM members m
    LEFT JOIN member_online_status mos ON m.id = mos.member_id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Comments and Documentation
-- ========================================

-- This schema provides:
-- 1. Enhanced photo management with chunked uploads
-- 2. Real-time member online/offline tracking
-- 3. Activity logging for analytics
-- 4. Optimized indexes for performance
-- 5. Automated cleanup procedures
-- 6. Foreign key constraints for data integrity
-- 7. Triggers for automatic status updates
-- 8. Views for easy data access

-- ========================================
-- Completion Status
-- ========================================
SELECT 'Enhanced photo system database setup completed successfully! (Fixed version - safe foreign key handling)' as Status;

-- Setup complete! Run cleanup procedures periodically via cron job:
-- */5 * * * * mysql -u username -p database_name -e "CALL cleanup_offline_members();"
-- 0 * * * * mysql -u username -p database_name -e "CALL cleanup_expired_upload_sessions();"

-- Begin enhanced_photo_system_duplicate_fix.sql

-- Enhanced Photo System - Duplicate Column Fix Script
-- This script safely handles duplicate column issues in the enhanced photo system

-- ========================================
-- DUPLICATE COLUMN FIX SCRIPT
-- ========================================

-- Disable foreign key checks for safe operation
SET FOREIGN_KEY_CHECKS = 0;

-- ========================================
-- Check and Fix Members Table Columns
-- ========================================

-- Remove duplicate photo_id column if it exists multiple times
-- (This addresses the #1060 - Duplicate column name 'photo_id' error)

-- First, check if photo_id column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'photo_id');

-- If column exists, we'll work with it; if not, we'll add it
SELECT @column_exists as photo_id_column_exists;

-- Check if last_activity_at column exists
SET @column_exists_activity = (SELECT COUNT(*) FROM information_schema.columns 
                               WHERE table_schema = DATABASE() 
                               AND table_name = 'members' 
                               AND column_name = 'last_activity_at');

SELECT @column_exists_activity as last_activity_at_column_exists;

-- Check if is_online column exists  
SET @column_exists_online = (SELECT COUNT(*) FROM information_schema.columns 
                             WHERE table_schema = DATABASE() 
                             AND table_name = 'members' 
                             AND column_name = 'is_online');

SELECT @column_exists_online as is_online_column_exists;

-- ========================================
-- Safe Column Addition (Only if not exists)
-- ========================================

-- Add photo_id column ONLY if it doesn't exist
SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN photo_id int(11) DEFAULT NULL AFTER photo_url;', 
    'SELECT "photo_id column already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add last_activity_at column ONLY if it doesn't exist
SET @sql = IF(@column_exists_activity = 0, 
    'ALTER TABLE members ADD COLUMN last_activity_at datetime DEFAULT NULL AFTER updated_at;', 
    'SELECT "last_activity_at column already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_online column ONLY if it doesn't exist
SET @sql = IF(@column_exists_online = 0, 
    'ALTER TABLE members ADD COLUMN is_online tinyint(1) DEFAULT 0 AFTER last_activity_at;', 
    'SELECT "is_online column already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Safe Index Addition (Only if not exists)
-- ========================================

-- Add photo_id index ONLY if it doesn't exist
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_photo_id');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_photo_id (photo_id);', 
    'SELECT "idx_photo_id index already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add last_activity index ONLY if it doesn't exist
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_last_activity');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_last_activity (last_activity_at);', 
    'SELECT "idx_last_activity index already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add online_status index ONLY if it doesn't exist
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'members' 
                     AND index_name = 'idx_online_status');

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE members ADD KEY idx_online_status (is_online);', 
    'SELECT "idx_online_status index already exists - skipping addition" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Check Current Members Table Structure
-- ========================================

-- Show current members table structure for verification
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    EXTRA
FROM information_schema.columns 
WHERE table_schema = DATABASE() 
AND table_name = 'members' 
AND COLUMN_NAME IN ('photo_id', 'last_activity_at', 'is_online')
ORDER BY ORDINAL_POSITION;

-- Show current indexes on members table
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM information_schema.statistics 
WHERE table_schema = DATABASE() 
AND table_name = 'members' 
AND INDEX_NAME IN ('idx_photo_id', 'idx_last_activity', 'idx_online_status')
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- ========================================
-- Alternative Fix for Duplicate Column Issues
-- ========================================

-- If you're still getting duplicate column errors, uncomment and run this section:

/*
-- Emergency fix: Drop and recreate columns if there are duplicates
-- WARNING: This will remove existing data in these columns!

-- Drop problematic columns if they exist
ALTER TABLE members DROP COLUMN IF EXISTS photo_id;
ALTER TABLE members DROP COLUMN IF EXISTS last_activity_at;
ALTER TABLE members DROP COLUMN IF EXISTS is_online;

-- Add them back cleanly
ALTER TABLE members ADD COLUMN photo_id int(11) DEFAULT NULL AFTER photo_url;
ALTER TABLE members ADD COLUMN last_activity_at datetime DEFAULT NULL AFTER updated_at;
ALTER TABLE members ADD COLUMN is_online tinyint(1) DEFAULT 0 AFTER last_activity_at;

-- Add indexes
ALTER TABLE members ADD KEY idx_photo_id (photo_id);
ALTER TABLE members ADD KEY idx_last_activity (last_activity_at);
ALTER TABLE members ADD KEY idx_online_status (is_online);
*/

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ========================================
-- Completion Status
-- ========================================
SELECT 'Enhanced photo system duplicate column fix completed successfully!' as Status;
SELECT 'If you still see duplicate column errors, uncomment the emergency fix section above.' as Note;

-- Begin enhanced_photo_system_migration.sql

-- Enhanced Photo System Migration Script
-- Safe migration that handles foreign key constraints properly

-- ========================================
-- Pre-migration Setup
-- ========================================

-- Disable foreign key checks temporarily for safe migration
SET FOREIGN_KEY_CHECKS = 0;

-- Check current table status
SELECT 
    'Current enhanced photo system tables status before migration' as status,
    table_name,
    table_schema
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('member_photos', 'member_online_status', 'photo_processing_queue');

-- ========================================
-- Safe Table Recreation
-- ========================================

-- Drop existing tables safely (foreign keys disabled)
DROP TABLE IF EXISTS `photo_processing_queue`;
DROP TABLE IF EXISTS `member_online_status`;
DROP TABLE IF EXISTS `member_photos`;

-- ========================================
-- Member Photos Table
-- ========================================
CREATE TABLE `member_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_size` bigint(20) NOT NULL,
  `upload_date` datetime DEFAULT current_timestamp(),
  `uploaded_by` int(11) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  `is_profile_photo` tinyint(1) DEFAULT 0,
  `dimensions_width` int(11) DEFAULT NULL,
  `dimensions_height` int(11) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `processing_status` enum('uploading','processing','completed','failed') DEFAULT 'uploading',
  `has_thumbnail` tinyint(1) DEFAULT 0,
  `has_optimized` tinyint(1) DEFAULT 0,
  `has_webp` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_uploaded_by` (`uploaded_by`),
  KEY `idx_profile_photo` (`member_id`, `is_profile_photo`),
  KEY `idx_upload_date` (`upload_date`),
  KEY `idx_processing_status` (`processing_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Member Online Status Table
-- ========================================
CREATE TABLE `member_online_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `is_online` tinyint(1) DEFAULT 0,
  `last_activity` datetime DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `last_logout` datetime DEFAULT NULL,
  `session_id` varchar(128) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `activity_type` enum('login','logout','page_view','api_call','idle','active') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_member_id` (`member_id`),
  KEY `idx_is_online` (`is_online`),
  KEY `idx_last_activity` (`last_activity`),
  KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Photo Processing Queue Table
-- ========================================
CREATE TABLE `photo_processing_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `photo_id` int(11) NOT NULL,
  `processing_type` enum('thumbnail','optimize','webp','all') DEFAULT 'all',
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `priority` int(11) DEFAULT 5,
  `attempts` int(11) DEFAULT 0,
  `max_attempts` int(11) DEFAULT 3,
  `error_message` text DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_photo_id` (`photo_id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Add Foreign Key Constraints (After table creation)
-- ========================================

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- Add foreign key constraints safely
ALTER TABLE `member_photos`
  ADD CONSTRAINT `fk_member_photos_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_member_photos_admin` FOREIGN KEY (`uploaded_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `member_online_status`
  ADD CONSTRAINT `fk_member_online_status_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE;

ALTER TABLE `photo_processing_queue`
  ADD CONSTRAINT `fk_photo_processing_queue_photo` FOREIGN KEY (`photo_id`) REFERENCES `member_photos` (`id`) ON DELETE CASCADE;

-- ========================================
-- Update Members Table for Photo Integration (Safe)
-- ========================================

-- Add photo_id column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'photo_id');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN photo_id int(11) DEFAULT NULL;', 
    'SELECT "photo_id column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add last_activity_at column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'last_activity_at');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN last_activity_at datetime DEFAULT NULL;', 
    'SELECT "last_activity_at column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_online column if it doesn't exist
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns 
                      WHERE table_schema = DATABASE() 
                      AND table_name = 'members' 
                      AND column_name = 'is_online');

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE members ADD COLUMN is_online tinyint(1) DEFAULT 0;', 
    'SELECT "is_online column already exists in members table" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add members to photos foreign key constraint if it doesn't exist
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.table_constraints 
                         WHERE table_schema = DATABASE() 
                         AND table_name = 'members' 
                         AND constraint_name = 'fk_members_photo');

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE members ADD CONSTRAINT fk_members_photo FOREIGN KEY (photo_id) REFERENCES member_photos (id) ON DELETE SET NULL;', 
    'SELECT "fk_members_photo constraint already exists" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- Create Indexes for Performance
-- ========================================

-- Additional composite indexes for better performance
CREATE INDEX idx_member_photos_member_status ON member_photos (member_id, processing_status);
CREATE INDEX idx_member_photos_upload_date_member ON member_photos (upload_date, member_id);
CREATE INDEX idx_online_status_activity_date ON member_online_status (last_activity, is_online);
CREATE INDEX idx_processing_queue_status_priority ON photo_processing_queue (status, priority);

-- ========================================
-- Add Settings for Enhanced Photo System
-- ========================================

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
('photo_max_file_size', '10485760', 'integer', 'Maximum file size for photo uploads in bytes (10MB)', 0, NOW(), NOW()),
('photo_allowed_types', 'jpg,jpeg,png,gif,webp', 'string', 'Allowed photo file types', 0, NOW(), NOW()),
('photo_chunk_size', '1048576', 'integer', 'Chunk size for photo uploads in bytes (1MB)', 0, NOW(), NOW()),
('photo_enable_webp', '1', 'boolean', 'Enable WebP format conversion', 0, NOW(), NOW()),
('photo_thumbnail_size', '150', 'integer', 'Thumbnail size in pixels', 0, NOW(), NOW()),
('photo_optimize_quality', '85', 'integer', 'JPEG optimization quality (1-100)', 0, NOW(), NOW()),
('photo_enable_lazy_loading', '1', 'boolean', 'Enable lazy loading for photos', 1, NOW(), NOW()),
('member_online_timeout', '300', 'integer', 'Member online timeout in seconds (5 minutes)', 0, NOW(), NOW()),
('realtime_status_update_interval', '30', 'integer', 'Real-time status update interval in seconds', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
`setting_value` = VALUES(`setting_value`),
`updated_at` = NOW();

-- ========================================
-- Create Triggers for Automatic Status Updates
-- ========================================

DELIMITER //

-- Trigger to update member online status on login
CREATE TRIGGER tr_member_login_status
    AFTER INSERT ON member_online_status
    FOR EACH ROW
BEGIN
    UPDATE member_online_status 
    SET is_online = 1, last_login = NOW()
    WHERE member_id = NEW.member_id;
END//

-- Trigger to automatically create processing queue entry for new photos
CREATE TRIGGER tr_photo_processing_queue
    AFTER INSERT ON member_photos
    FOR EACH ROW
BEGIN
    INSERT INTO photo_processing_queue (photo_id, processing_type, status, priority)
    VALUES (NEW.id, 'all', 'pending', 5);
END//

-- Trigger to update photo processing status
CREATE TRIGGER tr_photo_processing_status
    AFTER UPDATE ON photo_processing_queue
    FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' THEN
        UPDATE member_photos 
        SET processing_status = 'completed',
            has_thumbnail = 1,
            has_optimized = 1,
            has_webp = 1
        WHERE id = NEW.photo_id;
    ELSEIF NEW.status = 'failed' THEN
        UPDATE member_photos 
        SET processing_status = 'failed'
        WHERE id = NEW.photo_id;
    END IF;
END//

DELIMITER ;

-- ========================================
-- Sample Data for Testing
-- ========================================

-- Insert sample member online status (only for existing members)
INSERT INTO `member_online_status` (`member_id`, `is_online`, `last_activity`, `last_login`, `ip_address`, `activity_type`)
SELECT 
    `id`,
    0,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 60) MINUTE),
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 24) HOUR),
    CONCAT('192.168.1.', FLOOR(RAND() * 254) + 1),
    'page_view'
FROM `members`
WHERE `status` = 'approved'
LIMIT 10;

-- ========================================
-- Create Views for Easy Querying
-- ========================================

-- View for member photo summary
CREATE OR REPLACE VIEW member_photo_summary AS
SELECT 
    mp.id,
    mp.filename,
    mp.original_name,
    mp.file_size,
    mp.upload_date,
    mp.is_profile_photo,
    mp.processing_status,
    mp.has_thumbnail,
    mp.has_optimized,
    mp.has_webp,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    a.username as uploaded_by_name
FROM member_photos mp
LEFT JOIN members m ON mp.member_id = m.id
LEFT JOIN admins a ON mp.uploaded_by = a.id;

-- View for member online status
CREATE OR REPLACE VIEW member_status_view AS
SELECT 
    mos.id,
    mos.member_id,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    mos.is_online,
    mos.last_activity,
    mos.last_login,
    mos.activity_type,
    CASE 
        WHEN mos.is_online = 1 THEN 'Online'
        WHEN mos.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 'Recently Active'
        WHEN mos.last_activity > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 'Active within 1 hour'
        WHEN mos.last_activity > DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 'Active today'
        ELSE 'Offline'
    END as status_text,
    TIMESTAMPDIFF(MINUTE, mos.last_activity, NOW()) as minutes_since_activity
FROM member_online_status mos
LEFT JOIN members m ON mos.member_id = m.id;

-- ========================================
-- Verify Migration Success
-- ========================================

SELECT 
    'Enhanced photo system migration completed - Current table status' as status,
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('member_photos', 'member_online_status', 'photo_processing_queue')
ORDER BY table_name;

-- Check foreign key constraints
SELECT 
    'Foreign key constraints status' as status,
    constraint_name,
    table_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = DATABASE() 
AND referenced_table_name IS NOT NULL
AND table_name IN ('member_photos', 'member_online_status', 'photo_processing_queue')
ORDER BY table_name, constraint_name;

-- Final success message
SELECT 
    'Enhanced photo system migration completed successfully!' as status,
    'All tables created with proper foreign key constraints.' as message,
    'Photo processing queue and real-time status tracking are now active.' as features,
    NOW() as completed_at;

-- Begin fix_duplicate_keys_migration.sql

-- Migration Script to Fix Duplicate Key Issues and Function-Based Index Errors
-- Run this if you already have the tables created and need to fix them

-- Step 1: Check current table structures
SELECT 
    'Current table status before migration' as status,
    table_name,
    table_schema
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('receipts', 'invoices', 'receipt_templates');

-- Step 2: Fix receipts table if it exists
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'receipts');

-- Remove old problematic indexes first (function-based ones)
SET @sql = IF(@table_exists > 0, 
    'DROP INDEX IF EXISTS idx_receipts_receipt_number_year ON receipts;
     DROP INDEX IF EXISTS idx_receipts_member_year ON receipts;', 
    'SELECT "receipts table does not exist, skipping function-based index removal" as status;');

-- Note: MySQL doesn't support DROP INDEX IF EXISTS, so we'll use a different approach
-- Check and drop problematic indexes one by one
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_receipt_number_year');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'DROP INDEX idx_receipts_receipt_number_year ON receipts;', 
    'SELECT "idx_receipts_receipt_number_year does not exist or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_member_year');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'DROP INDEX idx_receipts_member_year ON receipts;', 
    'SELECT "idx_receipts_member_year does not exist or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix duplicate key issue if exists
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'receipt_number');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'ALTER TABLE receipts DROP INDEX receipt_number; ALTER TABLE receipts ADD UNIQUE KEY uk_receipt_number (receipt_number);', 
    'SELECT "No duplicate receipt_number index found" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 3: Fix invoices table if it exists
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'invoices');

-- Remove old problematic indexes first (function-based ones)
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'idx_invoices_invoice_number_year');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'DROP INDEX idx_invoices_invoice_number_year ON invoices;', 
    'SELECT "idx_invoices_invoice_number_year does not exist or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix duplicate key issue if exists
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'invoice_number');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'ALTER TABLE invoices DROP INDEX invoice_number; ALTER TABLE invoices ADD UNIQUE KEY uk_invoice_number (invoice_number);', 
    'SELECT "No duplicate invoice_number index found" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 4: Add proper indexes (non-function-based) if tables exist
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'receipts');

-- For receipts table - Add proper indexes
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_receipt_number_date');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_receipts_receipt_number_date ON receipts (receipt_number, created_at);', 
    'SELECT "receipts date index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_member_date');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_receipts_member_date ON receipts (member_id, created_at);', 
    'SELECT "receipts member date index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_amount');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_receipts_amount ON receipts (amount);', 
    'SELECT "receipts amount index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'receipts' AND index_name = 'idx_receipts_type_date');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_receipts_type_date ON receipts (transaction_type, created_at);', 
    'SELECT "receipts type date index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- For invoices table - Add proper indexes
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'invoices');

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'idx_invoices_invoice_number_date');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_invoices_invoice_number_date ON invoices (invoice_number, created_at);', 
    'SELECT "invoices date index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'idx_invoices_member_status');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_invoices_member_status ON invoices (member_id, status);', 
    'SELECT "invoices member status index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'idx_invoices_amount');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_invoices_amount ON invoices (total_amount);', 
    'SELECT "invoices amount index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'invoices' AND index_name = 'idx_invoices_due_date_status');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_invoices_due_date_status ON invoices (due_date, status);', 
    'SELECT "invoices due date status index already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 5: Verify the fix
SELECT 
    'Migration completed - Current index status' as status,
    table_name,
    index_name,
    column_name,
    non_unique
FROM information_schema.statistics 
WHERE table_schema = DATABASE() 
AND table_name IN ('receipts', 'invoices') 
AND (index_name LIKE '%receipt%' OR index_name LIKE '%invoice%' OR index_name LIKE '%date%' OR index_name LIKE '%amount%' OR index_name LIKE '%member%' OR index_name LIKE '%status%')
ORDER BY table_name, index_name;

-- Step 6: Show final status
SELECT 
    'Migration completed successfully!' as status,
    'All duplicate key issues and function-based index errors have been resolved.' as message,
    'The system is now compatible with MariaDB/MySQL.' as compatibility,
    NOW() as completed_at;

-- Begin email_setup.sql

-- Email Functionality Database Setup for KSO Chandigarh
-- Run these queries to add email functionality to the existing database

-- --------------------------------------------------------
-- Table structure for table `email_logs`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` varchar(255) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `template_used` varchar(100) DEFAULT NULL,
  `status` enum('sent','failed','pending') DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `sent_at` datetime DEFAULT current_timestamp(),
  `retry_count` int(11) DEFAULT 0,
  `user_id` int(11) DEFAULT NULL,
  `user_type` enum('admin','member') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_to_email` (`to_email`),
  KEY `idx_status` (`status`),
  KEY `idx_sent_at` (`sent_at`),
  KEY `idx_template_used` (`template_used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert email configuration settings
-- --------------------------------------------------------

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
('email_enabled', '0', 'boolean', 'Enable/Disable Email Service', 0, NOW(), NOW()),
('smtp_host', 'localhost', 'string', 'SMTP Server Host', 0, NOW(), NOW()),
('smtp_port', '587', 'integer', 'SMTP Server Port', 0, NOW(), NOW()),
('smtp_username', '', 'string', 'SMTP Username/Email', 0, NOW(), NOW()),
('smtp_password', '', 'string', 'SMTP Password (encrypted)', 0, NOW(), NOW()),
('smtp_encryption', 'tls', 'string', 'SMTP Encryption Type (tls/ssl/none)', 0, NOW(), NOW()),
('email_from_address', 'noreply@ksochandigarh.com', 'string', 'Default From Email Address', 0, NOW(), NOW()),
('email_from_name', 'KSO Chandigarh', 'string', 'Default From Name', 0, NOW(), NOW()),
('email_auto_welcome', '1', 'boolean', 'Send automatic welcome emails', 0, NOW(), NOW()),
('email_auto_approval', '1', 'boolean', 'Send automatic approval emails', 0, NOW(), NOW()),
('email_auto_events', '1', 'boolean', 'Send automatic event emails', 0, NOW(), NOW()),
('email_auto_fees', '1', 'boolean', 'Send automatic fee reminder emails', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
`setting_value` = VALUES(`setting_value`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Table structure for table `email_templates`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `body` longtext NOT NULL,
  `variables` json DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert default email templates
-- --------------------------------------------------------

INSERT INTO `email_templates` (`name`, `subject`, `body`, `variables`, `is_active`) VALUES
('welcome', 'Welcome to KSO Chandigarh!', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #007bff; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .highlight { background: #fff3cd; padding: 10px; border-radius: 5px; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>Welcome to KSO Chandigarh!</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            <p>Welcome to the Kashmiri Students Organization Chandigarh! Your membership application has been received and is being processed.</p>
            
            <div class="highlight">
                <p><strong>Your KSO ID:</strong> {{kso_id}}</p>
                <p><strong>Membership Type:</strong> {{membership_type}}</p>
                <p><strong>Application Date:</strong> {{current_date}}</p>
            </div>
            
            <p>We will review your application and notify you once it''s approved. This process typically takes 2-3 business days.</p>
            
            <p>If you have any questions, please feel free to contact us.</p>
            
            <p>Best regards,<br>
            <strong>KSO Chandigarh Team</strong></p>
        </div>
        <div class="footer">
            <p>&copy; {{current_year}} {{organization_name}}. All rights reserved.</p>
            <p>{{website_url}}</p>
        </div>
    </div>
</body>
</html>', 
'["first_name", "last_name", "kso_id", "membership_type"]', 1),

('approval', 'Your KSO Membership has been Approved!', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #28a745; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .success-box { background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 15px 0; border: 1px solid #c3e6cb; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>🎉 Congratulations!</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <div class="success-box">
                <h3>Your membership has been approved!</h3>
                <p><strong>KSO ID:</strong> {{kso_id}}</p>
                <p><strong>Status:</strong> Active Member</p>
            </div>
            
            <p>We''re excited to welcome you as an official member of the Kashmiri Students Organization Chandigarh!</p>
            
            <p>As a member, you now have access to:</p>
            <ul>
                <li>All KSO events and activities</li>
                <li>Cultural programs and celebrations</li>
                <li>Educational workshops and seminars</li>
                <li>Community networking opportunities</li>
                <li>Member-only resources and support</li>
            </ul>
            
            <p>Keep an eye on your email for upcoming events and important announcements.</p>
            
            <p>Welcome to the KSO family!</p>
            
            <p>Best regards,<br>
            <strong>KSO Chandigarh Team</strong></p>
        </div>
        <div class="footer">
            <p>&copy; {{current_year}} {{organization_name}}. All rights reserved.</p>
            <p>{{website_url}}</p>
        </div>
    </div>
</body>
</html>', 
'["first_name", "last_name", "kso_id"]', 1),

('rejection', 'KSO Membership Application Update', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #dc3545; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .info-box { background: #fff3cd; color: #856404; padding: 15px; border-radius: 5px; margin: 15px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>Membership Application Update</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <p>Thank you for your interest in joining the Kashmiri Students Organization Chandigarh.</p>
            
            <div class="info-box">
                <p>After careful review, we regret to inform you that your membership application could not be approved at this time.</p>
                {{#if reason}}
                <p><strong>Reason:</strong> {{reason}}</p>
                {{/if}}
            </div>
            
            <p>This decision does not reflect on your character or qualifications. You are welcome to reapply in the future.</p>
            
            <p>If you have any questions about this decision or would like guidance for future applications, please feel free to contact us.</p>
            
            <p>Thank you for your understanding.</p>
            
            <p>Best regards,<br>
            <strong>KSO Chandigarh Team</strong></p>
        </div>
        <div class="footer">
            <p>&copy; {{current_year}} {{organization_name}}. All rights reserved.</p>
            <p>{{website_url}}</p>
        </div>
    </div>
</body>
</html>', 
'["first_name", "last_name", "reason"]', 1),

('event-reminder', 'Event Reminder: {{event_title}}', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #17a2b8; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .event-details { background: #e2f3ff; padding: 15px; border-radius: 5px; margin: 15px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>📅 Event Reminder</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}},</h2>
            
            <p>This is a friendly reminder about the upcoming KSO event you''re registered for:</p>
            
            <div class="event-details">
                <h3>{{event_title}}</h3>
                <p><strong>📅 Date:</strong> {{event_date}}</p>
                <p><strong>⏰ Time:</strong> {{event_time}}</p>
                <p><strong>📍 Location:</strong> {{event_location}}</p>
            </div>
            
            <p>We''re looking forward to seeing you there! Please arrive on time and bring any required materials.</p>
            
            <p>If you can no longer attend, please let us know as soon as possible.</p>
            
            <p>See you at the event!</p>
            
            <p>Best regards,<br>
            <strong>KSO Chandigarh Events Team</strong></p>
        </div>
        <div class="footer">
            <p>&copy; {{current_year}} {{organization_name}}. All rights reserved.</p>
            <p>{{website_url}}</p>
        </div>
    </div>
</body>
</html>', 
'["first_name", "event_title", "event_date", "event_time", "event_location"]', 1),

('fee-reminder', 'Membership Fee Payment Reminder', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #ffc107; color: #212529; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .payment-details { background: #fff3cd; padding: 15px; border-radius: 5px; margin: 15px 0; border: 1px solid #ffeaa7; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>💳 Payment Reminder</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <p>This is a reminder that your KSO membership fee payment is due.</p>
            
            <div class="payment-details">
                <h3>Payment Details</h3>
                <p><strong>Member ID:</strong> {{kso_id}}</p>
                <p><strong>Amount Due:</strong> ₹{{fee_amount}}</p>
                <p><strong>Due Date:</strong> {{due_date}}</p>
            </div>
            
            <p>To maintain your active membership status, please complete your payment by the due date.</p>
            
            <p><strong>Payment Methods:</strong></p>
            <ul>
                <li>Cash payment at KSO office</li>
                <li>Bank transfer (contact us for details)</li>
                <li>Online payment (link will be provided)</li>
            </ul>
            
            <p>If you have already made the payment, please ignore this reminder or contact us to update your records.</p>
            
            <p>Thank you for your continued membership!</p>
            
            <p>Best regards,<br>
            <strong>KSO Chandigarh Finance Team</strong></p>
        </div>
        <div class="footer">
            <p>&copy; {{current_year}} {{organization_name}}. All rights reserved.</p>
            <p>{{website_url}}</p>
        </div>
    </div>
</body>
</html>', 
'["first_name", "last_name", "kso_id", "fee_amount", "due_date"]', 1)

ON DUPLICATE KEY UPDATE 
`subject` = VALUES(`subject`),
`body` = VALUES(`body`),
`variables` = VALUES(`variables`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Indexes for email functionality (avoiding duplicates)
-- --------------------------------------------------------

-- Email logs indexes - Only add if they don't already exist
-- The table already has idx_status and idx_sent_at, so we add composite indexes
ALTER TABLE `email_logs`
  ADD INDEX `idx_email_logs_composite_date_status` (`sent_at`, `status`),
  ADD INDEX `idx_email_logs_user_composite` (`user_id`, `user_type`);

-- Add email notification preferences to existing notification_settings table
ALTER TABLE `notification_settings` 
  ADD COLUMN IF NOT EXISTS `email_welcome` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_approval` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_events` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_fees` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_newsletter` tinyint(1) DEFAULT 1;

-- --------------------------------------------------------
-- Update existing members with default email preferences
-- --------------------------------------------------------

INSERT INTO `notification_settings` 
  (`user_id`, `user_type`, `email_notifications`, `email_welcome`, `email_approval`, `email_events`, `email_fees`, `email_newsletter`)
SELECT 
  `id`, 
  'member',
  1, 1, 1, 1, 1, 1
FROM `members`
WHERE `id` NOT IN (SELECT `user_id` FROM `notification_settings` WHERE `user_type` = 'member');

-- --------------------------------------------------------
-- Sample email log entries for testing (compatible with all MariaDB/MySQL versions)
-- --------------------------------------------------------

INSERT INTO `email_logs` (`to_email`, `subject`, `template_used`, `status`, `sent_at`) VALUES
('test@example.com', 'Welcome to KSO Chandigarh!', 'welcome', 'sent', DATE_SUB(NOW(), INTERVAL 1 DAY)),
('member@example.com', 'Your KSO Membership has been Approved!', 'approval', 'sent', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('user@example.com', 'Event Reminder: Cultural Festival', 'event-reminder', 'failed', DATE_SUB(NOW(), INTERVAL 1 HOUR));

COMMIT;

-- Begin email_system_migration.sql

-- Email System Database Migration Script
-- Fix duplicate key issues and MariaDB/MySQL compatibility problems

-- Step 1: Check current email tables
SELECT 
    'Current email tables status before migration' as status,
    table_name,
    table_schema
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('email_logs', 'email_templates', 'email_settings', 'notification_settings');

-- Step 2: Handle duplicate index issues in email_logs table
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'email_logs');

-- Remove any problematic duplicate indexes that might exist
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'email_logs' AND index_name = 'idx_email_logs_date_status');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'DROP INDEX idx_email_logs_date_status ON email_logs;', 
    'SELECT "idx_email_logs_date_status does not exist" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Remove any other potentially duplicate indexes
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'email_logs' AND index_name = 'idx_email_logs_user');
SET @sql = IF(@index_exists > 0 AND @table_exists > 0, 
    'DROP INDEX idx_email_logs_user ON email_logs;', 
    'SELECT "idx_email_logs_user does not exist" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 3: Add proper composite indexes if they don't exist (avoiding duplicates)
SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'email_logs' AND index_name = 'idx_date_status_composite');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_date_status_composite ON email_logs (sent_at, status);', 
    'SELECT "idx_date_status_composite already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'email_logs' AND index_name = 'idx_user_composite');
SET @sql = IF(@index_exists = 0 AND @table_exists > 0, 
    'CREATE INDEX idx_user_composite ON email_logs (user_id, user_type);', 
    'SELECT "idx_user_composite already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 4: Add email notification preferences to notification_settings if they don't exist
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'notification_settings');

-- Check if email_welcome column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'notification_settings' AND column_name = 'email_welcome');
SET @sql = IF(@column_exists = 0 AND @table_exists > 0, 
    'ALTER TABLE notification_settings ADD COLUMN email_welcome tinyint(1) DEFAULT 1;', 
    'SELECT "email_welcome column already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if email_approval column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'notification_settings' AND column_name = 'email_approval');
SET @sql = IF(@column_exists = 0 AND @table_exists > 0, 
    'ALTER TABLE notification_settings ADD COLUMN email_approval tinyint(1) DEFAULT 1;', 
    'SELECT "email_approval column already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if email_events column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'notification_settings' AND column_name = 'email_events');
SET @sql = IF(@column_exists = 0 AND @table_exists > 0, 
    'ALTER TABLE notification_settings ADD COLUMN email_events tinyint(1) DEFAULT 1;', 
    'SELECT "email_events column already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if email_fees column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'notification_settings' AND column_name = 'email_fees');
SET @sql = IF(@column_exists = 0 AND @table_exists > 0, 
    'ALTER TABLE notification_settings ADD COLUMN email_fees tinyint(1) DEFAULT 1;', 
    'SELECT "email_fees column already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if email_newsletter column exists
SET @column_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'notification_settings' AND column_name = 'email_newsletter');
SET @sql = IF(@column_exists = 0 AND @table_exists > 0, 
    'ALTER TABLE notification_settings ADD COLUMN email_newsletter tinyint(1) DEFAULT 1;', 
    'SELECT "email_newsletter column already exists or table missing" as status;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 5: Clear any problematic test data and add compatible test data
DELETE FROM email_logs WHERE to_email IN ('test@example.com', 'member@example.com', 'user@example.com');

-- Add compatible test data (avoiding DATE_SUB issues)
INSERT INTO `email_logs` (`to_email`, `subject`, `template_used`, `status`, `sent_at`) VALUES
('test@example.com', 'Welcome to KSO Chandigarh!', 'welcome', 'sent', DATE_SUB(NOW(), INTERVAL 1 DAY)),
('member@example.com', 'Your KSO Membership has been Approved!', 'approval', 'sent', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('user@example.com', 'Event Reminder: Cultural Festival', 'event-reminder', 'failed', DATE_SUB(NOW(), INTERVAL 1 HOUR));

-- Step 6: Verify the fix
SELECT 
    'Email system migration completed - Current index status' as status,
    table_name,
    index_name,
    column_name,
    non_unique
FROM information_schema.statistics 
WHERE table_schema = DATABASE() 
AND table_name IN ('email_logs', 'email_templates', 'notification_settings') 
AND (index_name LIKE '%email%' OR index_name LIKE '%composite%' OR index_name LIKE '%date%' OR index_name LIKE '%user%')
ORDER BY table_name, index_name;

-- Step 7: Show final status
SELECT 
    'Email system migration completed successfully!' as status,
    'All duplicate key issues and compatibility problems have been resolved.' as message,
    'Email system is now fully compatible with MariaDB/MySQL.' as compatibility,
    NOW() as completed_at;

-- Begin email_setup_fixed.sql

-- Email Functionality Database Setup for KSO Chandigarh - FIXED VERSION
-- Run these queries to add email functionality to the existing database
-- This version fixes potential duplicate index issues

-- --------------------------------------------------------
-- Table structure for table `email_logs`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` varchar(255) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `template_used` varchar(100) DEFAULT NULL,
  `status` enum('sent','failed','pending') DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `sent_at` datetime DEFAULT current_timestamp(),
  `retry_count` int(11) DEFAULT 0,
  `user_id` int(11) DEFAULT NULL,
  `user_type` enum('admin','member') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_to_email` (`to_email`),
  KEY `idx_status` (`status`),
  KEY `idx_sent_at` (`sent_at`),
  KEY `idx_template_used` (`template_used`),
  KEY `idx_user_composite` (`user_id`, `user_type`),
  KEY `idx_date_status_composite` (`sent_at`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert email configuration settings
-- --------------------------------------------------------

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
('email_enabled', '0', 'boolean', 'Enable/Disable Email Service', 0, NOW(), NOW()),
('smtp_host', 'localhost', 'string', 'SMTP Server Host', 0, NOW(), NOW()),
('smtp_port', '587', 'integer', 'SMTP Server Port', 0, NOW(), NOW()),
('smtp_username', '', 'string', 'SMTP Username/Email', 0, NOW(), NOW()),
('smtp_password', '', 'string', 'SMTP Password (encrypted)', 0, NOW(), NOW()),
('smtp_encryption', 'tls', 'string', 'SMTP Encryption Type (tls/ssl/none)', 0, NOW(), NOW()),
('email_from_address', 'noreply@ksochandigarh.com', 'string', 'Default From Email Address', 0, NOW(), NOW()),
('email_from_name', 'KSO Chandigarh', 'string', 'Default From Name', 0, NOW(), NOW()),
('email_auto_welcome', '1', 'boolean', 'Send automatic welcome emails', 0, NOW(), NOW()),
('email_auto_approval', '1', 'boolean', 'Send automatic approval emails', 0, NOW(), NOW()),
('email_auto_events', '1', 'boolean', 'Send automatic event emails', 0, NOW(), NOW()),
('email_auto_fees', '1', 'boolean', 'Send automatic fee reminder emails', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
`setting_value` = VALUES(`setting_value`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Table structure for table `email_templates`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `body` longtext NOT NULL,
  `variables` json DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_template_name` (`name`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert default email templates
-- --------------------------------------------------------

INSERT INTO `email_templates` (`name`, `subject`, `body`, `variables`, `is_active`) VALUES
('welcome', 'Welcome to KSO Chandigarh!', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #007bff; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .highlight { background: #fff3cd; padding: 10px; border-radius: 5px; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>Welcome to KSO Chandigarh!</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            <p>Welcome to the Kashmiri Students Organization Chandigarh! Your membership application has been received and is being processed.</p>
            
            <div class="highlight">
                <p><strong>Your KSO ID:</strong> {{kso_id}}</p>
                <p><strong>Membership Type:</strong> {{membership_type}}</p>
                <p><strong>Application Date:</strong> {{current_date}}</p>
            </div>
            
            <p>We will review your application and notify you once it''s approved. This process typically takes 2-3 business days.</p>
            
            <p>If you have any questions, please feel free to contact us.</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "kso_id", "membership_type", "current_date"]', 1),

('approval', 'Your KSO Membership has been Approved!', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #28a745; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .success { background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 10px 0; }
        .next-steps { background: #e3f2fd; padding: 15px; border-radius: 5px; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>🎉 Congratulations!</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <div class="success">
                <p><strong>Great news!</strong> Your KSO Chandigarh membership has been approved!</p>
            </div>
            
            <p><strong>Your Membership Details:</strong></p>
            <ul>
                <li><strong>KSO ID:</strong> {{kso_id}}</li>
                <li><strong>Membership Type:</strong> {{membership_type}}</li>
                <li><strong>Approved Date:</strong> {{current_date}}</li>
                <li><strong>Status:</strong> Active</li>
            </ul>
            
            <div class="next-steps">
                <h3>Next Steps:</h3>
                <ol>
                    <li>Log in to your member portal to update your profile</li>
                    <li>Check out upcoming events and activities</li>
                    <li>Connect with other members in the community</li>
                </ol>
            </div>
            
            <p>We''re excited to have you as part of the KSO family! Feel free to reach out if you have any questions.</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "kso_id", "membership_type", "current_date"]', 1),

('rejection', 'Update on Your KSO Membership Application', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #dc3545; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .info { background: #fff3cd; padding: 15px; border-radius: 5px; margin: 10px 0; }
        .next-steps { background: #e3f2fd; padding: 15px; border-radius: 5px; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>KSO Membership Update</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <p>Thank you for your interest in joining the Kashmiri Students Organization Chandigarh.</p>
            
            <div class="info">
                <p>After reviewing your application, we were unable to approve your membership at this time.</p>
                <p><strong>Reason:</strong> {{rejection_reason}}</p>
            </div>
            
            <div class="next-steps">
                <h3>What''s Next:</h3>
                <ul>
                    <li>You can address the concerns mentioned above</li>
                    <li>Feel free to reapply after making necessary updates</li>
                    <li>Contact us if you need clarification on the requirements</li>
                </ul>
            </div>
            
            <p>We appreciate your interest in KSO Chandigarh and encourage you to apply again in the future.</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "rejection_reason", "current_date"]', 1),

('event-reminder', 'Event Reminder: {{event_name}}', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #17a2b8; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .event-details { background: #e9ecef; padding: 15px; border-radius: 5px; margin: 10px 0; }
        .cta-button { background: #007bff; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>📅 Event Reminder</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <p>This is a friendly reminder about the upcoming KSO event:</p>
            
            <div class="event-details">
                <h3>{{event_name}}</h3>
                <p><strong>Date:</strong> {{event_date}}</p>
                <p><strong>Time:</strong> {{event_time}}</p>
                <p><strong>Location:</strong> {{event_location}}</p>
                <p><strong>Description:</strong> {{event_description}}</p>
            </div>
            
            <p>Don''t miss out on this exciting opportunity to connect with fellow members and participate in our community activities.</p>
            
            <a href="{{event_link}}" class="cta-button">View Event Details</a>
            
            <p>We look forward to seeing you there!</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "event_name", "event_date", "event_time", "event_location", "event_description", "event_link"]', 1),

('fee-reminder', 'Fee Reminder: {{fee_type}}', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #ffc107; color: #212529; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .fee-details { background: #fff3cd; padding: 15px; border-radius: 5px; margin: 10px 0; }
        .payment-button { background: #28a745; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 10px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>💰 Fee Reminder</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <p>This is a reminder about your upcoming fee payment:</p>
            
            <div class="fee-details">
                <h3>{{fee_type}}</h3>
                <p><strong>Amount:</strong> ₹{{fee_amount}}</p>
                <p><strong>Due Date:</strong> {{due_date}}</p>
                <p><strong>Your KSO ID:</strong> {{kso_id}}</p>
            </div>
            
            <p>Please make the payment by the due date to avoid any inconvenience. You can pay online through our member portal or contact us for other payment methods.</p>
            
            <a href="{{payment_link}}" class="payment-button">Pay Now</a>
            
            <p>If you have already made the payment, please ignore this reminder.</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "fee_type", "fee_amount", "due_date", "kso_id", "payment_link"]', 1),

('newsletter', 'KSO Newsletter - {{newsletter_title}}', '<!DOCTYPE html>
<html>
<head>
    <style>
        .email-container { max-width: 600px; margin: 0 auto; font-family: Arial, sans-serif; }
        .header { background: #6f42c1; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; background: #f8f9fa; }
        .footer { background: #343a40; color: white; padding: 15px; text-align: center; }
        .newsletter-content { background: white; padding: 20px; border-radius: 5px; margin: 10px 0; }
        .section { margin: 20px 0; }
    </style>
</head>
<body>
    <div class="email-container">
        <div class="header">
            <h1>📰 KSO Newsletter</h1>
        </div>
        <div class="content">
            <h2>Dear {{first_name}} {{last_name}},</h2>
            
            <div class="newsletter-content">
                <h3>{{newsletter_title}}</h3>
                <p>{{newsletter_content}}</p>
            </div>
            
            <div class="section">
                <h3>Upcoming Events</h3>
                <p>{{upcoming_events}}</p>
            </div>
            
            <div class="section">
                <h3>Community Updates</h3>
                <p>{{community_updates}}</p>
            </div>
            
            <p>Stay connected with KSO Chandigarh for more updates and opportunities!</p>
            
            <p>Best regards,<br>
            KSO Chandigarh Team</p>
        </div>
        <div class="footer">
            <p>Kashmiri Students Organization Chandigarh</p>
            <p>Email: info@ksochandigarh.com | Website: www.ksochandigarh.com</p>
            <p><a href="{{unsubscribe_link}}" style="color: #adb5bd;">Unsubscribe</a></p>
        </div>
    </div>
</body>
</html>', '["first_name", "last_name", "newsletter_title", "newsletter_content", "upcoming_events", "community_updates", "unsubscribe_link"]', 1)

ON DUPLICATE KEY UPDATE 
`subject` = VALUES(`subject`),
`body` = VALUES(`body`),
`variables` = VALUES(`variables`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Add email notification preferences to existing notification_settings table
-- --------------------------------------------------------

ALTER TABLE `notification_settings` 
  ADD COLUMN IF NOT EXISTS `email_welcome` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_approval` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_events` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_fees` tinyint(1) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `email_newsletter` tinyint(1) DEFAULT 1;

-- --------------------------------------------------------
-- Update existing members with default email preferences
-- --------------------------------------------------------

INSERT INTO `notification_settings` 
  (`user_id`, `user_type`, `email_notifications`, `email_welcome`, `email_approval`, `email_events`, `email_fees`, `email_newsletter`)
SELECT 
  `id`, 
  'member',
  1, 1, 1, 1, 1, 1
FROM `members`
WHERE `id` NOT IN (SELECT `user_id` FROM `notification_settings` WHERE `user_type` = 'member');

-- --------------------------------------------------------
-- Sample email log entries for testing (compatible with all MariaDB/MySQL versions)
-- --------------------------------------------------------

INSERT INTO `email_logs` (`to_email`, `subject`, `template_used`, `status`, `sent_at`) VALUES
('test@example.com', 'Welcome to KSO Chandigarh!', 'welcome', 'sent', DATE_SUB(NOW(), INTERVAL 1 DAY)),
('member@example.com', 'Your KSO Membership has been Approved!', 'approval', 'sent', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('user@example.com', 'Event Reminder: Cultural Festival', 'event-reminder', 'failed', DATE_SUB(NOW(), INTERVAL 1 HOUR));

-- --------------------------------------------------------
-- Completion message
-- --------------------------------------------------------

SELECT 'Email functionality database setup completed successfully! (Fixed version - no duplicate indexes)' as Status;

-- Begin receipt_invoice_setup.sql

-- Receipt and Invoice System Database Setup for KSO Chandigarh
-- Run these queries to add receipt and invoice functionality

-- --------------------------------------------------------
-- Table structure for table `receipts`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_number` varchar(50) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `transaction_type` enum('membership_fee','donation','event_fee','other') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` enum('pdf','html') DEFAULT 'html',
  `generated_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_receipt_number` (`receipt_number`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_receipts_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `finance` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_receipts_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_receipts_admin` FOREIGN KEY (`generated_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `invoices`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `member_id` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `due_date` date NOT NULL,
  `status` enum('pending','paid','overdue','cancelled') DEFAULT 'pending',
  `items` json NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `payment_transaction_id` int(11) DEFAULT NULL,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `paid_date` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_invoice_number` (`invoice_number`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_invoices_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_invoices_transaction` FOREIGN KEY (`payment_transaction_id`) REFERENCES `finance` (`id`),
  CONSTRAINT `fk_invoices_admin` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `receipt_templates`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `receipt_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `template_type` enum('receipt','invoice') NOT NULL DEFAULT 'receipt',
  `transaction_types` json DEFAULT NULL,
  `template_content` longtext NOT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_type` (`name`, `template_type`),
  KEY `idx_template_type` (`template_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert default receipt templates
-- --------------------------------------------------------

INSERT INTO `receipt_templates` (`name`, `template_type`, `transaction_types`, `template_content`, `is_default`, `is_active`) VALUES
('Standard Receipt', 'receipt', '["membership_fee", "event_fee", "donation", "other"]', 'standard', 1, 1),
('Membership Fee Receipt', 'receipt', '["membership_fee"]', 'membership', 0, 1),
('Event Fee Receipt', 'receipt', '["event_fee"]', 'event', 0, 1),
('Donation Receipt', 'receipt', '["donation"]', 'donation', 0, 1),
('Standard Invoice', 'invoice', '["membership_fee", "event_fee", "other"]', 'standard', 1, 1);

-- --------------------------------------------------------
-- Add receipt settings to settings table
-- --------------------------------------------------------

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
('receipt_auto_generate', '1', 'boolean', 'Automatically generate receipts for completed transactions', 0, NOW(), NOW()),
('receipt_email_auto_send', '1', 'boolean', 'Automatically email receipts to members', 0, NOW(), NOW()),
('receipt_prefix_membership', 'KSO-MF-', 'string', 'Receipt number prefix for membership fees', 0, NOW(), NOW()),
('receipt_prefix_event', 'KSO-EF-', 'string', 'Receipt number prefix for event fees', 0, NOW(), NOW()),
('receipt_prefix_donation', 'KSO-DN-', 'string', 'Receipt number prefix for donations', 0, NOW(), NOW()),
('receipt_prefix_other', 'KSO-OT-', 'string', 'Receipt number prefix for other transactions', 0, NOW(), NOW()),
('organization_address', 'Chandigarh, India', 'string', 'Organization address for receipts', 1, NOW(), NOW()),
('organization_phone', '+91-XXXXX-XXXXX', 'string', 'Organization phone for receipts', 1, NOW(), NOW()),
('organization_email', 'info@ksochandigarh.com', 'string', 'Organization email for receipts', 1, NOW(), NOW()),
('organization_website', 'https://ksochandigarh.com', 'string', 'Organization website for receipts', 1, NOW(), NOW()),
('receipt_footer_text', 'Thank you for your payment. This is a computer-generated receipt.', 'string', 'Footer text for receipts', 0, NOW(), NOW()),
('invoice_terms_conditions', 'Payment is due within 30 days of invoice date. Late payments may incur additional charges.', 'text', 'Terms and conditions for invoices', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
`setting_value` = VALUES(`setting_value`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Create indexes for better performance
-- --------------------------------------------------------

-- Additional indexes for receipts
CREATE INDEX idx_receipts_receipt_number_year ON receipts (receipt_number, YEAR(created_at));
CREATE INDEX idx_receipts_member_year ON receipts (member_id, YEAR(created_at));
CREATE INDEX idx_receipts_amount ON receipts (amount);

-- Additional indexes for invoices
CREATE INDEX idx_invoices_invoice_number_year ON invoices (invoice_number, YEAR(created_at));
CREATE INDEX idx_invoices_member_status ON invoices (member_id, status);
CREATE INDEX idx_invoices_amount ON invoices (total_amount);

-- --------------------------------------------------------
-- Create triggers for automatic receipt generation
-- --------------------------------------------------------

DELIMITER //

-- Trigger to auto-generate receipt when finance record is marked as completed
CREATE TRIGGER tr_finance_receipt_auto_generate
    AFTER UPDATE ON finance
    FOR EACH ROW
BEGIN
    -- Only generate receipt if status changed to completed and no receipt exists
    IF NEW.status = 'completed' AND OLD.status != 'completed' AND NEW.receipt_number IS NOT NULL THEN
        -- Check if receipt already exists
        IF NOT EXISTS (SELECT 1 FROM receipts WHERE transaction_id = NEW.id) THEN
            -- Insert receipt record (actual PDF generation will be handled by PHP)
            INSERT INTO receipts (
                receipt_number, transaction_id, member_id, transaction_type, 
                amount, file_path, generated_by, created_at
            ) VALUES (
                NEW.receipt_number, NEW.id, NEW.member_id, NEW.transaction_type,
                NEW.amount, CONCAT('pending_generation_', NEW.receipt_number), 
                NEW.processed_by, NOW()
            );
        END IF;
    END IF;
END//

-- Trigger to update invoice status when payment is received
CREATE TRIGGER tr_invoice_payment_update
    AFTER INSERT ON finance
    FOR EACH ROW
BEGIN
    -- Check if this payment is for an invoice
    IF NEW.notes LIKE '%Invoice:%' THEN
        -- Extract invoice number from notes
        SET @invoice_number = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.notes, 'Invoice:', -1), ' ', 1);
        
        -- Update invoice status and payment details
        UPDATE invoices 
        SET 
            status = 'paid',
            payment_transaction_id = NEW.id,
            paid_amount = NEW.amount,
            paid_date = NEW.payment_date
        WHERE invoice_number = @invoice_number AND status = 'pending';
    END IF;
END//

DELIMITER ;

-- --------------------------------------------------------
-- Sample data for testing (optional)
-- --------------------------------------------------------

-- Insert sample receipt records for existing transactions
-- INSERT INTO receipts (receipt_number, transaction_id, member_id, transaction_type, amount, file_path, generated_by)
-- SELECT 
--     CONCAT('KSO-MF-', DATE_FORMAT(payment_date, '%Y%m'), LPAD(ROW_NUMBER() OVER (ORDER BY id), 4, '0')),
--     id, member_id, transaction_type, amount, 
--     CONCAT('storage/receipts/', YEAR(payment_date), '/', receipt_number, '.html'),
--     processed_by
-- FROM finance 
-- WHERE status = 'completed' AND receipt_number IS NOT NULL
-- LIMIT 10;

-- --------------------------------------------------------
-- Views for easy reporting
-- --------------------------------------------------------

-- Receipt summary view
CREATE OR REPLACE VIEW receipt_summary AS
SELECT 
    r.id,
    r.receipt_number,
    r.transaction_type,
    r.amount,
    r.created_at as receipt_date,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    m.email as member_email,
    f.payment_method,
    f.payment_date,
    a.username as generated_by_name
FROM receipts r
LEFT JOIN members m ON r.member_id = m.id
LEFT JOIN finance f ON r.transaction_id = f.id
LEFT JOIN admins a ON r.generated_by = a.id;

-- Invoice summary view
CREATE OR REPLACE VIEW invoice_summary AS
SELECT 
    i.id,
    i.invoice_number,
    i.total_amount,
    i.due_date,
    i.status,
    i.paid_amount,
    i.paid_date,
    i.created_at as invoice_date,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    m.email as member_email,
    DATEDIFF(CURDATE(), i.due_date) as days_overdue
FROM invoices i
LEFT JOIN members m ON i.member_id = m.id;

-- --------------------------------------------------------
-- Grant permissions (adjust as needed for your setup)
-- --------------------------------------------------------

-- GRANT SELECT, INSERT, UPDATE, DELETE ON receipts TO 'kso_user'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO 'kso_user'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON receipt_templates TO 'kso_user'@'localhost';

-- --------------------------------------------------------
-- Completion message
-- --------------------------------------------------------

SELECT 'Receipt and Invoice system database setup completed successfully!' as Status;

-- Begin receipt_invoice_setup_fixed.sql

-- Fixed Database Setup for Receipt/Invoice System
-- This file fixes the duplicate key issues and ensures proper setup

-- First, let's check if tables exist and drop them if they do to avoid conflicts
SET FOREIGN_KEY_CHECKS = 0;

-- Drop tables in correct order to avoid foreign key constraint issues
DROP TABLE IF EXISTS `receipts`;
DROP TABLE IF EXISTS `invoices`;
DROP TABLE IF EXISTS `receipt_templates`;

SET FOREIGN_KEY_CHECKS = 1;

-- --------------------------------------------------------
-- Table structure for table `receipts` (FIXED)
-- --------------------------------------------------------

CREATE TABLE `receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_number` varchar(50) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `transaction_type` enum('membership_fee','donation','event_fee','other') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` enum('pdf','html') DEFAULT 'html',
  `generated_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_receipt_number` (`receipt_number`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_receipts_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `finance` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_receipts_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_receipts_admin` FOREIGN KEY (`generated_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `invoices` (FIXED)
-- --------------------------------------------------------

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `member_id` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `due_date` date NOT NULL,
  `status` enum('pending','paid','overdue','cancelled') DEFAULT 'pending',
  `items` json NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `payment_transaction_id` int(11) DEFAULT NULL,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `paid_date` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_invoice_number` (`invoice_number`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_invoices_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_invoices_transaction` FOREIGN KEY (`payment_transaction_id`) REFERENCES `finance` (`id`),
  CONSTRAINT `fk_invoices_admin` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `receipt_templates`
-- --------------------------------------------------------

CREATE TABLE `receipt_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `template_type` enum('receipt','invoice') NOT NULL DEFAULT 'receipt',
  `transaction_types` json DEFAULT NULL,
  `template_content` longtext NOT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name_type` (`name`, `template_type`),
  KEY `idx_template_type` (`template_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Insert default receipt templates
-- --------------------------------------------------------

INSERT INTO `receipt_templates` (`name`, `template_type`, `transaction_types`, `template_content`, `is_default`, `is_active`) VALUES
('Standard Receipt', 'receipt', '["membership_fee", "event_fee", "donation", "other"]', 'standard', 1, 1),
('Membership Fee Receipt', 'receipt', '["membership_fee"]', 'membership', 0, 1),
('Event Fee Receipt', 'receipt', '["event_fee"]', 'event', 0, 1),
('Donation Receipt', 'receipt', '["donation"]', 'donation', 0, 1),
('Standard Invoice', 'invoice', '["membership_fee", "event_fee", "other"]', 'standard', 1, 1);

-- --------------------------------------------------------
-- Add receipt settings to settings table (with proper handling)
-- --------------------------------------------------------

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
('receipt_auto_generate', '1', 'boolean', 'Automatically generate receipts for completed transactions', 0, NOW(), NOW()),
('receipt_email_auto_send', '1', 'boolean', 'Automatically email receipts to members', 0, NOW(), NOW()),
('receipt_prefix_membership', 'KSO-MF-', 'string', 'Receipt number prefix for membership fees', 0, NOW(), NOW()),
('receipt_prefix_event', 'KSO-EF-', 'string', 'Receipt number prefix for event fees', 0, NOW(), NOW()),
('receipt_prefix_donation', 'KSO-DN-', 'string', 'Receipt number prefix for donations', 0, NOW(), NOW()),
('receipt_prefix_other', 'KSO-OT-', 'string', 'Receipt number prefix for other transactions', 0, NOW(), NOW()),
('organization_address', 'Chandigarh, India', 'string', 'Organization address for receipts', 1, NOW(), NOW()),
('organization_phone', '+91-XXXXX-XXXXX', 'string', 'Organization phone for receipts', 1, NOW(), NOW()),
('organization_email', 'info@ksochandigarh.com', 'string', 'Organization email for receipts', 1, NOW(), NOW()),
('organization_website', 'https://ksochandigarh.com', 'string', 'Organization website for receipts', 1, NOW(), NOW()),
('receipt_footer_text', 'Thank you for your payment. This is a computer-generated receipt.', 'string', 'Footer text for receipts', 0, NOW(), NOW()),
('invoice_terms_conditions', 'Payment is due within 30 days of invoice date. Late payments may incur additional charges.', 'text', 'Terms and conditions for invoices', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
`setting_value` = VALUES(`setting_value`),
`updated_at` = NOW();

-- --------------------------------------------------------
-- Create additional indexes for better performance
-- --------------------------------------------------------

-- Additional indexes for receipts - Using multi-column indexes instead of function-based
-- These indexes help with date-based queries and performance without using unsupported YEAR() function
CREATE INDEX idx_receipts_receipt_number_date ON receipts (receipt_number, created_at);
CREATE INDEX idx_receipts_member_date ON receipts (member_id, created_at);
CREATE INDEX idx_receipts_amount ON receipts (amount);
CREATE INDEX idx_receipts_type_date ON receipts (transaction_type, created_at);

-- Additional indexes for invoices - Using multi-column indexes instead of function-based
-- These indexes help with date-based queries and performance without using unsupported YEAR() function
CREATE INDEX idx_invoices_invoice_number_date ON invoices (invoice_number, created_at);
CREATE INDEX idx_invoices_member_status ON invoices (member_id, status);
CREATE INDEX idx_invoices_amount ON invoices (total_amount);
CREATE INDEX idx_invoices_due_date_status ON invoices (due_date, status);

-- --------------------------------------------------------
-- Create triggers for automatic receipt generation
-- --------------------------------------------------------

DELIMITER //

-- Drop existing triggers if they exist
DROP TRIGGER IF EXISTS tr_finance_receipt_auto_generate//
DROP TRIGGER IF EXISTS tr_invoice_payment_update//

-- Trigger to auto-generate receipt when finance record is marked as completed
CREATE TRIGGER tr_finance_receipt_auto_generate
    AFTER UPDATE ON finance
    FOR EACH ROW
BEGIN
    -- Only generate receipt if status changed to completed and no receipt exists
    IF NEW.status = 'completed' AND OLD.status != 'completed' AND NEW.receipt_number IS NOT NULL THEN
        -- Check if receipt already exists
        IF NOT EXISTS (SELECT 1 FROM receipts WHERE transaction_id = NEW.id) THEN
            -- Insert receipt record (actual PDF generation will be handled by PHP)
            INSERT INTO receipts (
                receipt_number, transaction_id, member_id, transaction_type, 
                amount, file_path, generated_by, created_at
            ) VALUES (
                NEW.receipt_number, NEW.id, NEW.member_id, NEW.transaction_type,
                NEW.amount, CONCAT('pending_generation_', NEW.receipt_number), 
                NEW.processed_by, NOW()
            );
        END IF;
    END IF;
END//

-- Trigger to update invoice status when payment is received
CREATE TRIGGER tr_invoice_payment_update
    AFTER INSERT ON finance
    FOR EACH ROW
BEGIN
    -- Check if this payment is for an invoice
    IF NEW.notes LIKE '%Invoice:%' THEN
        -- Extract invoice number from notes
        SET @invoice_number = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.notes, 'Invoice:', -1), ' ', 1);
        
        -- Update invoice status and payment details
        UPDATE invoices 
        SET 
            status = 'paid',
            payment_transaction_id = NEW.id,
            paid_amount = NEW.amount,
            paid_date = NEW.payment_date
        WHERE invoice_number = @invoice_number AND status = 'pending';
    END IF;
END//

DELIMITER ;

-- --------------------------------------------------------
-- Create views for easy reporting
-- --------------------------------------------------------

-- Receipt summary view
CREATE OR REPLACE VIEW receipt_summary AS
SELECT 
    r.id,
    r.receipt_number,
    r.transaction_type,
    r.amount,
    r.created_at as receipt_date,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    m.email as member_email,
    f.payment_method,
    f.payment_date,
    a.username as generated_by_name
FROM receipts r
LEFT JOIN members m ON r.member_id = m.id
LEFT JOIN finance f ON r.transaction_id = f.id
LEFT JOIN admins a ON r.generated_by = a.id;

-- Invoice summary view
CREATE OR REPLACE VIEW invoice_summary AS
SELECT 
    i.id,
    i.invoice_number,
    i.total_amount,
    i.due_date,
    i.status,
    i.paid_amount,
    i.paid_date,
    i.created_at as invoice_date,
    CONCAT(m.first_name, ' ', m.last_name) as member_name,
    m.kso_id,
    m.email as member_email,
    DATEDIFF(CURDATE(), i.due_date) as days_overdue
FROM invoices i
LEFT JOIN members m ON i.member_id = m.id;

-- --------------------------------------------------------
-- Test the setup
-- --------------------------------------------------------

-- Verify tables were created successfully
SELECT 
    'receipts' as table_name,
    COUNT(*) as table_exists
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'receipts'
UNION ALL
SELECT 
    'invoices' as table_name,
    COUNT(*) as table_exists
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'invoices'
UNION ALL
SELECT 
    'receipt_templates' as table_name,
    COUNT(*) as table_exists
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'receipt_templates';

-- --------------------------------------------------------
-- Completion message
-- --------------------------------------------------------

SELECT 'Receipt and Invoice system database setup completed successfully! All duplicate key issues have been resolved.' as Status;
