-- Run this SQL in phpMyAdmin to fix the database tables
-- This will DROP and recreate tables with correct columns

-- Drop existing tables if they have wrong structure
DROP TABLE IF EXISTS `client_payments`;
DROP TABLE IF EXISTS `clients`;

-- Create clients table with correct structure
CREATE TABLE `clients` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `broker_id` INT NOT NULL,
    `full_name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NULL,
    `phone` VARCHAR(50) NOT NULL,
    `national_id` VARCHAR(50) NULL,
    `address` TEXT NULL,
    `notes` TEXT NULL,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `access_status` ENUM('granted', 'revoked', 'pending') DEFAULT 'granted',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_broker_id` (`broker_id`),
    INDEX `idx_phone` (`phone`),
    INDEX `idx_status` (`status`),
    INDEX `idx_access_status` (`access_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create client_payments table with correct structure
CREATE TABLE `client_payments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT NOT NULL,
    `amount` DECIMAL(15,2) NOT NULL,
    `payment_year` INT NOT NULL,
    `payment_date` DATE NOT NULL,
    `payment_method` ENUM('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other') DEFAULT 'cash',
    `reference_number` VARCHAR(100) NULL,
    `notes` TEXT NULL,
    `status` ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending',
    `recorded_by` INT NOT NULL,
    `confirmed_by` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_client_id` (`client_id`),
    INDEX `idx_payment_year` (`payment_year`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create payment_settings table if not exists
CREATE TABLE IF NOT EXISTS `payment_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default payment settings
INSERT IGNORE INTO `payment_settings` (`setting_key`, `setting_value`) VALUES
('yearly_fee', '50000'),
('currency', 'TZS'),
('payment_deadline_month', '3'),
('payment_deadline_day', '31');

-- Ensure activity_logs table exists
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `action` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
