-- =====================================================
-- COMPLETE DATABASE MIGRATION FOR ENKAGE-BO
-- =====================================================
-- This file contains all database tables and structures
-- for the Enkage Back Office system
-- 
-- Created: 2025-01-31
-- Version: 1.0
-- =====================================================

-- Enable UUID extension if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================================================
-- 1. ORGANIZATIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS organizations (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for organizations
CREATE INDEX IF NOT EXISTS idx_organizations_is_active ON organizations(is_active);

-- =====================================================
-- 2. USERS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    email VARCHAR(128) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    user_type VARCHAR(20) NOT NULL DEFAULT 'user' CHECK (user_type IN ('admin', 'user', 'moderator')),
    organization_id BIGINT REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    last_login TIMESTAMP,
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for users
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_user_type ON users(user_type);
CREATE INDEX IF NOT EXISTS idx_users_organization_id ON users(organization_id);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);

-- =====================================================
-- 3. ORGANIZATION UNITS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS organization_units (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    layer INTEGER NOT NULL CHECK (layer >= 1 AND layer <= 10),
    parent_id BIGINT REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE SET NULL,
    area_of_operation VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for organization_units
CREATE INDEX IF NOT EXISTS idx_organization_units_organization_id ON organization_units(organization_id);
CREATE INDEX IF NOT EXISTS idx_organization_units_parent_id ON organization_units(parent_id);
CREATE INDEX IF NOT EXISTS idx_organization_units_layer ON organization_units(layer);
CREATE INDEX IF NOT EXISTS idx_organization_units_is_active ON organization_units(is_active);

-- =====================================================
-- 4. DEPARTMENTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS departments (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for departments
CREATE INDEX IF NOT EXISTS idx_departments_organization_id ON departments(organization_id);
CREATE INDEX IF NOT EXISTS idx_departments_is_active ON departments(is_active);

-- =====================================================
-- 5. POSITIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS positions (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    title VARCHAR(128) NOT NULL,
    description TEXT,
    term_years INTEGER CHECK (term_years > 0),
    can_delegate BOOLEAN NOT NULL DEFAULT false,
    applicable_layers VARCHAR(32),
    departments VARCHAR(255),
    permissions JSONB DEFAULT '{}',
    layer_access JSONB DEFAULT '{}',
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for positions
CREATE INDEX IF NOT EXISTS idx_positions_organization_id ON positions(organization_id);
CREATE INDEX IF NOT EXISTS idx_positions_is_active ON positions(is_active);

-- =====================================================
-- 6. LOCATIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS locations (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    code VARCHAR(16),
    type VARCHAR(20) NOT NULL CHECK (type IN ('country', 'state', 'district')),
    parent_id BIGINT REFERENCES locations(id) ON UPDATE CASCADE ON DELETE SET NULL,
    level INTEGER NOT NULL DEFAULT 1 CHECK (level >= 1 AND level <= 3),
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for locations
CREATE INDEX IF NOT EXISTS idx_locations_type ON locations(type);
CREATE INDEX IF NOT EXISTS idx_locations_parent_id ON locations(parent_id);
CREATE INDEX IF NOT EXISTS idx_locations_level ON locations(level);
CREATE INDEX IF NOT EXISTS idx_locations_is_active ON locations(is_active);
CREATE INDEX IF NOT EXISTS idx_locations_name ON locations(name);

-- =====================================================
-- 7. MEMBERS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS members (
    id BIGSERIAL PRIMARY KEY,
    member_id VARCHAR(20) NOT NULL UNIQUE,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    layer1_id BIGINT REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE SET NULL,
    layer2_id BIGINT REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE SET NULL,
    layer3_id BIGINT REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE SET NULL,
    parent_member_id BIGINT REFERENCES members(id) ON UPDATE CASCADE ON DELETE SET NULL,
    is_family_member BOOLEAN NOT NULL DEFAULT false,
    
    -- Personal Information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    civil_id_number VARCHAR(50) UNIQUE,
    aadhar_number VARCHAR(50) UNIQUE,
    passport_number VARCHAR(50),
    date_of_birth DATE,
    gender VARCHAR(10) CHECK (gender IN ('male', 'female', 'other')),
    blood_group VARCHAR(5) CHECK (blood_group IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-')),
    
    -- Referral Information
    referred_by_member BOOLEAN NOT NULL DEFAULT false,
    referring_member_id BIGINT REFERENCES members(id) ON UPDATE CASCADE ON DELETE SET NULL,
    
    -- Contact Information
    email VARCHAR(255) UNIQUE,
    alternate_email VARCHAR(255),
    kuwait_mobile VARCHAR(20),
    indian_mobile1 VARCHAR(20),
    indian_mobile2 VARCHAR(20),
    whatsapp_number VARCHAR(20),
    phone VARCHAR(20),
    mobile VARCHAR(20),
    
    -- Primary Address
    primary_country VARCHAR(100),
    primary_state VARCHAR(100),
    primary_district VARCHAR(100),
    primary_city VARCHAR(100),
    primary_area VARCHAR(100),
    primary_block VARCHAR(50),
    primary_street VARCHAR(255),
    primary_house_number VARCHAR(50),
    primary_apartment VARCHAR(50),
    primary_floor VARCHAR(20),
    primary_landmark VARCHAR(255),
    primary_postal_code VARCHAR(20),
    
    -- Secondary Address
    secondary_country VARCHAR(100),
    secondary_state VARCHAR(100),
    secondary_district VARCHAR(100),
    secondary_city VARCHAR(100),
    secondary_area VARCHAR(100),
    secondary_block VARCHAR(50),
    secondary_street VARCHAR(255),
    secondary_house_number VARCHAR(50),
    secondary_apartment VARCHAR(50),
    secondary_floor VARCHAR(20),
    secondary_landmark VARCHAR(255),
    secondary_postal_code VARCHAR(20),
    
    -- Employment Details
    employment_status VARCHAR(20) CHECK (employment_status IN ('employed', 'self-employed')),
    job_title VARCHAR(255),
    work_location VARCHAR(255),
    years_of_experience INTEGER,
    salary_range VARCHAR(100),
    professional_interests JSON,
    working_organization_name VARCHAR(255),
    
    -- Membership Details
    membership_type VARCHAR(20) CHECK (membership_type IN ('regular', 'contact', 'magnet')),
    membership_status VARCHAR(20) CHECK (membership_status IN ('active', 'inactive', 'suspended', 'expired', 'pending', 'deceased', 'cancelled', 'cancelled overseas', 'on vacation')),
    membership_date DATE,
    expiry_date DATE,
    renewal_date DATE,
    membership_fee DECIMAL(10, 2),
    payment_status VARCHAR(20) CHECK (payment_status IN ('paid', 'pending', 'overdue', 'waived')),
    
    -- Emergency Contacts
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    emergency_contact_relationship VARCHAR(100),
    emergency_contact2_name VARCHAR(255),
    emergency_contact2_phone VARCHAR(20),
    emergency_contact2_relationship VARCHAR(100),
    
    -- Document Uploads
    civil_id_copy VARCHAR(255),
    aadhar_card_copy VARCHAR(255),
    profile_picture VARCHAR(500),
    documents JSONB,
    
    -- System Fields
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    updated_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    
    -- Registration Approval Fields
    registration_status VARCHAR(30) NOT NULL DEFAULT 'draft' CHECK (registration_status IN ('draft', 'pending_approval', 'approved', 'rejected', 'active', 'inactive')),
    approval_flow JSONB,
    
    -- Payment Verification Fields
    payment_verified BOOLEAN NOT NULL DEFAULT false,
    payment_verified_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    payment_verified_at TIMESTAMP,
    
    -- Approval Fields
    approved_at TIMESTAMP,
    approved_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for members
CREATE INDEX IF NOT EXISTS idx_members_member_id ON members(member_id);
CREATE INDEX IF NOT EXISTS idx_members_organization_id ON members(organization_id);
CREATE INDEX IF NOT EXISTS idx_members_parent_member_id ON members(parent_member_id);
CREATE INDEX IF NOT EXISTS idx_members_is_family_member ON members(is_family_member);
CREATE INDEX IF NOT EXISTS idx_members_civil_id_number ON members(civil_id_number);
CREATE INDEX IF NOT EXISTS idx_members_aadhar_number ON members(aadhar_number);
CREATE INDEX IF NOT EXISTS idx_members_email ON members(email);
CREATE INDEX IF NOT EXISTS idx_members_is_active ON members(is_active);
CREATE INDEX IF NOT EXISTS idx_members_layer1_id ON members(layer1_id);
CREATE INDEX IF NOT EXISTS idx_members_layer2_id ON members(layer2_id);
CREATE INDEX IF NOT EXISTS idx_members_layer3_id ON members(layer3_id);

-- =====================================================
-- 8. COMMITTEES TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS committees (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    name VARCHAR(128) NOT NULL,
    unit_id BIGINT NOT NULL REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE CASCADE,
    layer_id BIGINT NOT NULL REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE CASCADE,
    layer INTEGER NOT NULL CHECK (layer IN (1, 2, 3)),
    duration_from DATE,
    duration_to DATE,
    departments VARCHAR(255),
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for committees
CREATE INDEX IF NOT EXISTS idx_committees_organization_id ON committees(organization_id);
CREATE INDEX IF NOT EXISTS idx_committees_unit_id ON committees(unit_id);
CREATE INDEX IF NOT EXISTS idx_committees_layer_id ON committees(layer_id);
CREATE INDEX IF NOT EXISTS idx_committees_layer ON committees(layer);
CREATE INDEX IF NOT EXISTS idx_committees_is_active ON committees(is_active);

-- =====================================================
-- 9. COMMITTEE MEMBERS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS committee_members (
    id BIGSERIAL PRIMARY KEY,
    committee_id BIGINT NOT NULL REFERENCES committees(id) ON UPDATE CASCADE ON DELETE CASCADE,
    member_id BIGINT NOT NULL,
    user_id BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    position_id BIGINT REFERENCES positions(id) ON UPDATE CASCADE ON DELETE SET NULL,
    department_id BIGINT REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL,
    layer_id BIGINT REFERENCES organization_units(id) ON UPDATE CASCADE ON DELETE SET NULL,
    is_delegated BOOLEAN NOT NULL DEFAULT false,
    delegated_for_id BIGINT,
    term_from DATE,
    term_to DATE,
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_approver BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for committee_members
CREATE INDEX IF NOT EXISTS idx_committee_members_committee_id ON committee_members(committee_id);
CREATE INDEX IF NOT EXISTS idx_committee_members_member_id ON committee_members(member_id);
CREATE INDEX IF NOT EXISTS idx_committee_members_position_id ON committee_members(position_id);
CREATE INDEX IF NOT EXISTS idx_committee_members_department_id ON committee_members(department_id);
CREATE INDEX IF NOT EXISTS idx_committee_members_layer_id ON committee_members(layer_id);
CREATE INDEX IF NOT EXISTS idx_committee_members_is_active ON committee_members(is_active);

-- =====================================================
-- 10. COMMITTEE MEMBER DEPARTMENTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS committee_member_departments (
    id BIGSERIAL PRIMARY KEY,
    committee_member_id BIGINT NOT NULL REFERENCES committee_members(id) ON UPDATE CASCADE ON DELETE CASCADE,
    department_id BIGINT NOT NULL REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_committee_member_department UNIQUE (committee_member_id, department_id)
);

-- Indexes for committee_member_departments
CREATE INDEX IF NOT EXISTS idx_committee_member_departments_committee_member_id ON committee_member_departments(committee_member_id);
CREATE INDEX IF NOT EXISTS idx_committee_member_departments_department_id ON committee_member_departments(department_id);

-- =====================================================
-- 11. COMMITTEE MEMBER HISTORY TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS committee_member_history (
    id BIGSERIAL PRIMARY KEY,
    committee_member_id BIGINT NOT NULL REFERENCES committee_members(id) ON UPDATE CASCADE ON DELETE CASCADE,
    committee_id BIGINT NOT NULL REFERENCES committees(id) ON UPDATE CASCADE ON DELETE CASCADE,
    member_id BIGINT REFERENCES members(id) ON UPDATE CASCADE ON DELETE SET NULL,
    user_id BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    position_id BIGINT REFERENCES positions(id) ON UPDATE CASCADE ON DELETE SET NULL,
    department_id BIGINT REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL,
    is_delegated BOOLEAN DEFAULT false,
    delegated_for_id BIGINT,
    term_from DATE,
    term_to DATE,
    is_active BOOLEAN DEFAULT true,
    action VARCHAR(20) NOT NULL CHECK (action IN ('created', 'updated', 'deleted', 'activated', 'deactivated')),
    action_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    action_reason VARCHAR(500),
    previous_data JSONB,
    new_data JSONB,
    effective_from TIMESTAMP NOT NULL DEFAULT NOW(),
    effective_to TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes for committee_member_history
CREATE INDEX IF NOT EXISTS idx_committee_member_history_committee_member_id ON committee_member_history(committee_member_id);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_committee_id ON committee_member_history(committee_id);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_user_id ON committee_member_history(user_id);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_member_id ON committee_member_history(member_id);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_action ON committee_member_history(action);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_action_by ON committee_member_history(action_by);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_effective_from ON committee_member_history(effective_from);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_effective_to ON committee_member_history(effective_to);
CREATE INDEX IF NOT EXISTS idx_committee_member_history_created_at ON committee_member_history(created_at);

-- =====================================================
-- 12. FORM APPROVAL FLOWS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS form_approval_flows (
    id BIGSERIAL PRIMARY KEY,
    form_type VARCHAR(100) NOT NULL,
    form_name VARCHAR(200) NOT NULL,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    approval_levels JSONB NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    requires_all_levels BOOLEAN NOT NULL DEFAULT true,
    allow_parallel_approval BOOLEAN NOT NULL DEFAULT false,
    created_by BIGINT NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
    updated_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for form_approval_flows
CREATE INDEX IF NOT EXISTS idx_form_approval_flows_organization_id_form_type ON form_approval_flows(organization_id, form_type);
CREATE INDEX IF NOT EXISTS idx_form_approval_flows_is_active ON form_approval_flows(is_active);

-- =====================================================
-- 13. TRANSACTIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS transactions (
    id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR(50) NOT NULL UNIQUE,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    member_id BIGINT REFERENCES members(id) ON UPDATE CASCADE ON DELETE SET NULL,
    transaction_type VARCHAR(30) NOT NULL CHECK (transaction_type IN (
        'membership_fee', 'renewal_fee', 'late_fee', 'fine', 'event_fee', 
        'course_fee', 'donation', 'refund', 'adjustment', 'committee_fee', 
        'special_assessment', 'registration_fee'
    )),
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'KWD',
    payment_status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending', 'completed', 'failed', 'cancelled', 'refunded')),
    payment_method VARCHAR(50),
    due_date TIMESTAMP,
    paid_at TIMESTAMP,
    description TEXT,
    reference VARCHAR(100),
    form_type VARCHAR(100),
    form_id BIGINT,
    approval_flow JSONB,
    processed_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    created_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    updated_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for transactions
CREATE INDEX IF NOT EXISTS idx_transactions_transaction_id ON transactions(transaction_id);
CREATE INDEX IF NOT EXISTS idx_transactions_organization_id ON transactions(organization_id);
CREATE INDEX IF NOT EXISTS idx_transactions_member_id ON transactions(member_id);
CREATE INDEX IF NOT EXISTS idx_transactions_transaction_type ON transactions(transaction_type);
CREATE INDEX IF NOT EXISTS idx_transactions_payment_status ON transactions(payment_status);
CREATE INDEX IF NOT EXISTS idx_transactions_form_type ON transactions(form_type);
CREATE INDEX IF NOT EXISTS idx_transactions_form_id ON transactions(form_id);
CREATE INDEX IF NOT EXISTS idx_transactions_created_at ON transactions(created_at);

-- =====================================================
-- 14. TRANSACTION PAYMENTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS transaction_payments (
    id BIGSERIAL PRIMARY KEY,
    transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON UPDATE CASCADE ON DELETE CASCADE,
    payment_id VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'KWD',
    payment_method VARCHAR(20) NOT NULL CHECK (payment_method IN (
        'cash', 'bank_transfer', 'credit_card', 'debit_card', 'check', 
        'online_payment', 'mobile_payment', 'refund', 'other'
    )),
    payment_status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending', 'completed', 'failed', 'cancelled', 'refunded')),
    payment_reference VARCHAR(100),
    payment_date TIMESTAMP NOT NULL,
    receipt_number VARCHAR(50),
    notes TEXT,
    is_active BOOLEAN DEFAULT true,
    created_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    updated_by BIGINT REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for transaction_payments
CREATE INDEX IF NOT EXISTS idx_transaction_payments_payment_id ON transaction_payments(payment_id);
CREATE INDEX IF NOT EXISTS idx_transaction_payments_transaction_id ON transaction_payments(transaction_id);
CREATE INDEX IF NOT EXISTS idx_transaction_payments_payment_status ON transaction_payments(payment_status);
CREATE INDEX IF NOT EXISTS idx_transaction_payments_payment_date ON transaction_payments(payment_date);
CREATE INDEX IF NOT EXISTS idx_transaction_payments_is_active ON transaction_payments(is_active);

-- =====================================================
-- 15. ORGANIZATION SETTINGS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS organization_settings (
    id BIGSERIAL PRIMARY KEY,
    organization_id BIGINT NOT NULL REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
    setting_key VARCHAR(64) NOT NULL,
    setting_value TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_organization_setting UNIQUE (organization_id, setting_key)
);

-- Indexes for organization_settings
CREATE INDEX IF NOT EXISTS idx_organization_settings_organization_id ON organization_settings(organization_id);
CREATE INDEX IF NOT EXISTS idx_organization_settings_setting_key ON organization_settings(setting_key);

-- =====================================================
-- TRIGGERS FOR UPDATED_AT TIMESTAMPS
-- =====================================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply updated_at triggers to all tables
CREATE TRIGGER update_organizations_updated_at BEFORE UPDATE ON organizations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_organization_units_updated_at BEFORE UPDATE ON organization_units FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_departments_updated_at BEFORE UPDATE ON departments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_positions_updated_at BEFORE UPDATE ON positions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_locations_updated_at BEFORE UPDATE ON locations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_members_updated_at BEFORE UPDATE ON members FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_committees_updated_at BEFORE UPDATE ON committees FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_committee_members_updated_at BEFORE UPDATE ON committee_members FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_committee_member_departments_updated_at BEFORE UPDATE ON committee_member_departments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_committee_member_history_updated_at BEFORE UPDATE ON committee_member_history FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_form_approval_flows_updated_at BEFORE UPDATE ON form_approval_flows FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_transactions_updated_at BEFORE UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_transaction_payments_updated_at BEFORE UPDATE ON transaction_payments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_organization_settings_updated_at BEFORE UPDATE ON organization_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- COMMENTS FOR DOCUMENTATION
-- =====================================================

COMMENT ON TABLE organizations IS 'Main organizations table for multi-tenant support';
COMMENT ON TABLE users IS 'System users with authentication and authorization';
COMMENT ON TABLE organization_units IS 'Hierarchical organizational structure (layers 1-10)';
COMMENT ON TABLE departments IS 'Departments within organizations';
COMMENT ON TABLE positions IS 'Committee positions with permissions and access control';
COMMENT ON TABLE locations IS 'Geographic locations hierarchy (country -> state -> district)';
COMMENT ON TABLE members IS 'Member directory with complete profile information';
COMMENT ON TABLE committees IS 'Committee definitions with layer and department assignments';
COMMENT ON TABLE committee_members IS 'Committee member assignments with positions and terms';
COMMENT ON TABLE committee_member_departments IS 'Many-to-many relationship between committee members and departments';
COMMENT ON TABLE committee_member_history IS 'Audit trail for committee member changes';
COMMENT ON TABLE form_approval_flows IS 'Configurable approval workflows for different form types';
COMMENT ON TABLE transactions IS 'Financial transactions and payments';
COMMENT ON TABLE transaction_payments IS 'Payment records linked to transactions';
COMMENT ON TABLE organization_settings IS 'Organization-specific configuration settings';

-- =====================================================
-- MIGRATION COMPLETE
-- =====================================================

-- Display completion message
DO $$
BEGIN
    RAISE NOTICE '=====================================================';
    RAISE NOTICE 'ENKAGE-BO DATABASE MIGRATION COMPLETED SUCCESSFULLY';
    RAISE NOTICE '=====================================================';
    RAISE NOTICE 'Created % tables with all indexes and constraints', 15;
    RAISE NOTICE 'All triggers for updated_at timestamps are active';
    RAISE NOTICE 'Database is ready for use';
    RAISE NOTICE '=====================================================';
END $$;
