-- Funeral Policy Manager Database Schema
-- MySQL Database Setup Script

-- Create database
CREATE DATABASE IF NOT EXISTS funeral_policy_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE funeral_policy_db;

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('client', 'branch_admin', 'admin') NOT NULL,
    branch_id INT NULL,
    status ENUM('active', 'suspended', 'deleted') DEFAULT 'active',
    device_token TEXT NULL,
    push_token TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_phone (phone),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_branch_id (branch_id),
    INDEX idx_status (status)
);

-- Branches table
CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location TEXT NULL,
    contact TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_name (name)
);

-- Policy types table
CREATE TABLE policy_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL,
    premium DECIMAL(10,2) NOT NULL,
    max_members INT NOT NULL,
    created_by INT NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_active (active),
    INDEX idx_created_by (created_by)
);

-- Policies table
CREATE TABLE policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    policy_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    policy_type_id INT NOT NULL,
    branch_id INT NOT NULL,
    start_date DATE NOT NULL,
    status ENUM('active', 'suspended', 'archived') DEFAULT 'active',
    last_payment DATE NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_policy_number (policy_number),
    INDEX idx_user_id (user_id),
    INDEX idx_policy_type_id (policy_type_id),
    INDEX idx_branch_id (branch_id),
    INDEX idx_status (status),
    INDEX idx_last_payment (last_payment)
);

-- Policy members table
CREATE TABLE policy_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    policy_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('main', 'spouse', 'child', 'parent', 'extended') NOT NULL,
    dob DATE NOT NULL,
    documents TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_policy_id (policy_id),
    INDEX idx_type (type),
    INDEX idx_name (name)
);

-- Payments table
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    policy_id INT NOT NULL,
    branch_id INT NOT NULL,
    method VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    paid_on DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_policy_id (policy_id),
    INDEX idx_branch_id (branch_id),
    INDEX idx_paid_on (paid_on),
    INDEX idx_method (method)
);

-- Claims table
CREATE TABLE claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    policy_id INT NOT NULL,
    member_id INT NOT NULL,
    submitted_by ENUM('client', 'branch') NOT NULL,
    status ENUM('submitted', 'queued', 'approved', 'rejected', 'paid') DEFAULT 'submitted',
    notes TEXT NULL,
    admin_notes TEXT NULL,
    branch_notes TEXT NULL,
    rejection_reason TEXT NULL,
    queued_at DATETIME NULL,
    queued_by INT NULL,
    rejected_at DATETIME NULL,
    rejected_by INT NULL,
    paid_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_policy_id (policy_id),
    INDEX idx_member_id (member_id),
    INDEX idx_status (status),
    INDEX idx_queued_by (queued_by),
    INDEX idx_rejected_by (rejected_by)
);

-- Claim payouts table
CREATE TABLE claim_payouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    claim_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(50) NOT NULL,
    payout_date DATE NOT NULL,
    reference_number VARCHAR(100) NULL,
    beneficiary_name VARCHAR(100) NULL,
    beneficiary_account VARCHAR(100) NULL,
    notes TEXT NULL,
    paid_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_claim_id (claim_id),
    INDEX idx_payout_date (payout_date),
    INDEX idx_paid_by (paid_by),
    INDEX idx_reference_number (reference_number)
);

-- Tickets table
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_number VARCHAR(20) NOT NULL UNIQUE,
    subject VARCHAR(255) NOT NULL,
    context_type ENUM('policy', 'claim', 'payment', 'general') NOT NULL,
    context_id INT NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    created_by INT NOT NULL,
    assigned_to INT NULL,
    status ENUM('open', 'admin_replied', 'customer_replied', 'closed') DEFAULT 'open',
    resolved_at DATETIME NULL,
    resolved_by INT NULL,
    last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_ticket_number (ticket_number),
    INDEX idx_context (context_type, context_id),
    INDEX idx_created_by (created_by),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_last_activity (last_activity)
);

-- Ticket messages table
CREATE TABLE ticket_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    message TEXT NOT NULL,
    sent_by INT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_ticket_id (ticket_id),
    INDEX idx_sent_by (sent_by),
    INDEX idx_created_at (created_at),
    INDEX idx_is_internal (is_internal)
);

-- Signatures table
CREATE TABLE signatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    signed_by INT NOT NULL,
    context_type ENUM('claim', 'policy') NOT NULL,
    context_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_signed_by (signed_by),
    INDEX idx_context (context_type, context_id),
    INDEX idx_created_at (created_at)
);

-- Notifications table
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    read_status BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_read_status (read_status),
    INDEX idx_created_at (created_at)
);

-- User tokens table
CREATE TABLE user_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_token (token(255))
);

-- Create default admin user (password: admin123)
INSERT INTO users (name, phone, email, password, role, status) VALUES 
('System Administrator', '0123456789', 'admin@funeral.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Create sample branch
INSERT INTO branches (name, location, contact) VALUES 
('Main Branch', 'Cape Town, South Africa', 'Tel: 021-123-4567, Email: main@funeral.co.za');

-- Create sample policy type
INSERT INTO policy_types (name, description, premium, max_members, created_by, active) VALUES 
('Family Plan', 'Comprehensive family funeral policy', 150.00, 6, 1, TRUE),
('Individual Plan', 'Basic individual funeral policy', 75.00, 1, 1, TRUE);

-- Create indexes for performance
CREATE INDEX idx_policies_user_status ON policies(user_id, status);
CREATE INDEX idx_claims_policy_status ON claims(policy_id, status);
CREATE INDEX idx_payments_policy_date ON payments(policy_id, paid_on DESC);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, read_status);

-- Grant permissions (adjust username/password as needed)
-- GRANT ALL PRIVILEGES ON funeral_policy_db.* TO 'funeral_user'@'localhost' IDENTIFIED BY 'funeral_password';
-- FLUSH PRIVILEGES;
