-- ============================================================
-- Ghana Anaesthesiologists Society - Membership Management System
-- Complete Database Schema (MariaDB 10.4 compatible)
-- ============================================================

-- Database: `projects_membershipdb`
USE `projects_membershipdb`;

-- ============================================================
-- HELPER: Add column if not exists
-- ============================================================
DROP PROCEDURE IF EXISTS `add_column_if_not_exists`;
DELIMITER ;;
CREATE PROCEDURE `add_column_if_not_exists`(
    IN p_table VARCHAR(100),
    IN p_column VARCHAR(100),
    IN p_definition TEXT
)
BEGIN
    DECLARE col_count INT;
    SELECT COUNT(*) INTO col_count
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'projects_membershipdb'
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column;

    IF col_count = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;;
DELIMITER ;

-- ============================================================
-- EXTEND USERS TABLE (add membership columns)
-- ============================================================
CALL add_column_if_not_exists('users', 'first_name', 'VARCHAR(255) DEFAULT NULL AFTER `name`');
CALL add_column_if_not_exists('users', 'other_names', 'VARCHAR(255) DEFAULT NULL AFTER `first_name`');
CALL add_column_if_not_exists('users', 'last_name', 'VARCHAR(255) DEFAULT NULL AFTER `other_names`');
CALL add_column_if_not_exists('users', 'medical_council_number', 'VARCHAR(100) DEFAULT NULL AFTER `last_name`');
CALL add_column_if_not_exists('users', 'professional_pin', 'VARCHAR(100) DEFAULT NULL AFTER `medical_council_number`');
CALL add_column_if_not_exists('users', 'approval_status', "ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending' AFTER `status`");
CALL add_column_if_not_exists('users', 'approved_by', 'INT DEFAULT NULL AFTER `approval_status`');
CALL add_column_if_not_exists('users', 'approved_at', 'TIMESTAMP NULL DEFAULT NULL AFTER `approved_by`');
CALL add_column_if_not_exists('users', 'profile_complete', 'TINYINT(1) DEFAULT 0 AFTER `approved_at`');
CALL add_column_if_not_exists('users', 'last_reminder_sent', 'TIMESTAMP NULL DEFAULT NULL AFTER `profile_complete`');
CALL add_column_if_not_exists('users', 'membership_category', 'VARCHAR(100) DEFAULT NULL AFTER `last_reminder_sent`');
CALL add_column_if_not_exists('users', 'notification_preferences', 'TEXT DEFAULT NULL AFTER `membership_category`');

-- ============================================================
-- MEMBER PROFILES
-- ============================================================
CREATE TABLE IF NOT EXISTS `member_profiles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL UNIQUE,
    `gender` ENUM('male','female','other') DEFAULT NULL,
    `nationality` VARCHAR(100) DEFAULT NULL,
    `postal_address` TEXT DEFAULT NULL,
    `residential_address` TEXT DEFAULT NULL,
    `mobile_phone` VARCHAR(50) DEFAULT NULL,
    `alternative_phone` VARCHAR(50) DEFAULT NULL,
    `institution_hospital` VARCHAR(255) DEFAULT NULL,
    `city_town` VARCHAR(255) DEFAULT NULL,
    `country` VARCHAR(100) DEFAULT NULL,
    `years_practice` INT DEFAULT NULL,
    `highest_qualification` VARCHAR(255) DEFAULT NULL,
    `membership_category` VARCHAR(100) DEFAULT NULL,
    `previous_member` TINYINT(1) DEFAULT 0,
    `notify_conferences` TINYINT(1) DEFAULT 1,
    `notify_workshops` TINYINT(1) DEFAULT 1,
    `notify_webinars` TINYINT(1) DEFAULT 1,
    `notify_research` TINYINT(1) DEFAULT 0,
    `notify_scholarships` TINYINT(1) DEFAULT 0,
    `notify_news` TINYINT(1) DEFAULT 1,
    `declaration_initials` VARCHAR(10) DEFAULT NULL,
    `declaration_date` DATE DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- ANNOUNCEMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    `published_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- EVENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `event_type` ENUM('conference','workshop','webinar','meeting','social','other') NOT NULL DEFAULT 'meeting',
    `venue` VARCHAR(255) DEFAULT NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME DEFAULT NULL,
    `registration_deadline` DATETIME DEFAULT NULL,
    `max_attendees` INT DEFAULT NULL,
    `fee` DECIMAL(10,2) DEFAULT 0.00,
    `status` ENUM('upcoming','ongoing','completed','cancelled') NOT NULL DEFAULT 'upcoming',
    `cover_image` VARCHAR(255) DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- EVENT ATTENDANCE
-- ============================================================
CREATE TABLE IF NOT EXISTS `event_attendance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `event_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `status` ENUM('registered','attended','absent','cancelled') NOT NULL DEFAULT 'registered',
    `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `attended_at` TIMESTAMP NULL DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_attendance` (`event_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- MESSAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS `messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `sender_id` INT NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `priority` ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
    `has_attachment` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `message_recipients` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `message_id` INT NOT NULL,
    `recipient_id` INT NOT NULL,
    `is_read` TINYINT(1) DEFAULT 0,
    `read_at` TIMESTAMP NULL DEFAULT NULL,
    `is_archived` TINYINT(1) DEFAULT 0,
    FOREIGN KEY (`message_id`) REFERENCES `messages`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`recipient_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- FORUM
-- ============================================================
CREATE TABLE IF NOT EXISTS `forum_categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_topics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `is_pinned` TINYINT(1) DEFAULT 0,
    `is_locked` TINYINT(1) DEFAULT 0,
    `views` INT DEFAULT 0,
    `last_post_at` TIMESTAMP NULL DEFAULT NULL,
    `last_post_by` INT DEFAULT NULL,
    `status` ENUM('active','closed','hidden') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `forum_categories`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_posts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `topic_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `body` TEXT NOT NULL,
    `is_solution` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`topic_id`) REFERENCES `forum_topics`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- DOCUMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `document_categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `documents` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT DEFAULT NULL,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `file_type` VARCHAR(100) DEFAULT NULL,
    `file_size` INT DEFAULT NULL,
    `is_public` TINYINT(1) DEFAULT 0,
    `download_count` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `document_categories`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- PHOTOS / GALLERY
-- ============================================================
CREATE TABLE IF NOT EXISTS `photo_albums` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `cover_image` VARCHAR(255) DEFAULT NULL,
    `is_public` TINYINT(1) DEFAULT 1,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `photos` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `album_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `file_size` INT DEFAULT NULL,
    `width` INT DEFAULT NULL,
    `height` INT DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`album_id`) REFERENCES `photo_albums`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- CUSTOM WEB LINKS
-- ============================================================
CREATE TABLE IF NOT EXISTS `custom_links` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `icon` VARCHAR(100) DEFAULT 'fas fa-link',
    `target` ENUM('_self','_blank') NOT NULL DEFAULT '_blank',
    `sort_order` INT DEFAULT 0,
    `category` VARCHAR(100) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- CONTRIBUTIONS & RECEIPTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `contribution_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `frequency` ENUM('one-time','monthly','quarterly','annual') NOT NULL DEFAULT 'one-time',
    `is_mandatory` TINYINT(1) DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `contributions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `contribution_type_id` INT NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `payment_method` VARCHAR(100) DEFAULT NULL,
    `transaction_ref` VARCHAR(255) DEFAULT NULL,
    `payment_date` DATE NOT NULL,
    `status` ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
    `notes` TEXT DEFAULT NULL,
    `processed_by` INT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`contribution_type_id`) REFERENCES `contribution_types`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `receipts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contribution_id` INT NOT NULL,
    `receipt_number` VARCHAR(50) NOT NULL UNIQUE,
    `issued_date` DATE NOT NULL,
    `issued_by` INT NOT NULL,
    `file_path` VARCHAR(255) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`contribution_id`) REFERENCES `contributions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- DYNAMIC FORMS
-- ============================================================
CREATE TABLE IF NOT EXISTS `dynamic_forms` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `table_name` VARCHAR(100) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `requires_auth` TINYINT(1) DEFAULT 1,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `dynamic_form_fields` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `form_id` INT NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `field_name` VARCHAR(100) NOT NULL,
    `field_type` ENUM('text','textarea','email','number','select','checkbox','radio','date','file','password','url') NOT NULL DEFAULT 'text',
    `options` TEXT DEFAULT NULL,
    `is_required` TINYINT(1) DEFAULT 0,
    `placeholder` VARCHAR(255) DEFAULT NULL,
    `validation_rules` VARCHAR(255) DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`form_id`) REFERENCES `dynamic_forms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `dynamic_form_submissions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `form_id` INT NOT NULL,
    `user_id` INT DEFAULT NULL,
    `data` JSON NOT NULL,
    `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`form_id`) REFERENCES `dynamic_forms`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- EXTEND AUDIT LOGS
-- ============================================================
CALL add_column_if_not_exists('audit_logs', 'old_values', 'JSON DEFAULT NULL AFTER `description`');
CALL add_column_if_not_exists('audit_logs', 'new_values', 'JSON DEFAULT NULL AFTER `old_values`');

-- ============================================================
-- SEED DATA
-- ============================================================

-- Insert demo admin user for the society (password: password123)
INSERT INTO `users` (`name`, `first_name`, `last_name`, `email`, `password`, `role`, `status`, `approval_status`)
SELECT 'System Admin', 'System', 'Admin', 'admin@ghanaanaesthesia.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'admin', 'active', 'approved'
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'admin@ghanaanaesthesia.com');

-- Insert demo forum categories
INSERT INTO `forum_categories` (`name`, `description`, `sort_order`)
SELECT * FROM (
    SELECT 'General Discussion' AS `name`, 'General topics about anaesthesiology practice' AS `description`, 1 AS `sort_order`
    UNION ALL SELECT 'Clinical Cases', 'Share and discuss interesting clinical cases', 2
    UNION ALL SELECT 'Research & Publications', 'Discuss research findings and publications', 3
    UNION ALL SELECT 'Career & Training', 'Career opportunities, training programs, and fellowships', 4
    UNION ALL SELECT 'Technology & Equipment', 'Discussion about anaesthesia equipment and technology', 5
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `forum_categories` LIMIT 1);

-- Insert demo document categories
INSERT INTO `document_categories` (`name`, `description`, `sort_order`)
SELECT * FROM (
    SELECT 'Guidelines & Protocols' AS `name`, 'Clinical guidelines and standard operating procedures' AS `description`, 1 AS `sort_order`
    UNION ALL SELECT 'Forms & Templates', 'Downloadable forms and document templates', 2
    UNION ALL SELECT 'Publications', 'Society publications and newsletters', 3
    UNION ALL SELECT 'Meeting Minutes', 'Minutes from society meetings and AGMs', 4
    UNION ALL SELECT 'Educational Resources', 'Training materials and educational content', 5
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `document_categories` LIMIT 1);

-- Insert demo contribution types
INSERT INTO `contribution_types` (`name`, `description`, `amount`, `frequency`, `is_mandatory`)
SELECT * FROM (
    SELECT 'Annual Membership Dues' AS `name`, 'Standard annual membership subscription fee' AS `description`, 500.00 AS `amount`, 'annual' AS `frequency`, 1 AS `is_mandatory`
    UNION ALL SELECT 'Conference Registration', 'Annual scientific conference registration', 200.00, 'one-time', 0
    UNION ALL SELECT 'Workshop Fee', 'Specialized workshop participation fee', 150.00, 'one-time', 0
    UNION ALL SELECT 'Society Development Fund', 'Voluntary contribution to society development', 100.00, 'one-time', 0
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `contribution_types` LIMIT 1);

-- Cleanup
DROP PROCEDURE IF EXISTS `add_column_if_not_exists`;