-- Create database
CREATE DATABASE IF NOT EXISTS zylus_allocation;
USE zylus_allocation;

-- Clients table
CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_code VARCHAR(20) UNIQUE NOT NULL,
    fullname VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    id_type ENUM('nin', 'driver_license', 'passport', 'voter_card') DEFAULT 'nin',
    id_number VARCHAR(50),
    client_type ENUM('individual', 'corporate') DEFAULT 'individual',
    status ENUM('active', 'inactive', 'pending') DEFAULT 'active',
    assigned_agent_id INT,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_agent_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_client_code (client_code),
    INDEX idx_email (email),
    INDEX idx_phone (phone)
);

-- Real Estate Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    product_type ENUM('land', 'building') NOT NULL,
    location VARCHAR(200),
    estate_name VARCHAR(100),
    total_units INT DEFAULT 0,
    available_units INT DEFAULT 0,
    allocated_units INT DEFAULT 0,
    price_per_unit DECIMAL(15,2),
    status ENUM('available', 'fully_allocated', 'under_maintenance', 'coming_soon') DEFAULT 'available',
    allocation_format VARCHAR(50), -- e.g., "House {number}", "Plot {letter}"
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_product_code (product_code),
    INDEX idx_product_type (product_type),
    INDEX idx_status (status)
);

-- Product Units table (individual units within a product)
CREATE TABLE product_units (
    id INT PRIMARY KEY AUTO_INCREMENT,
    unit_code VARCHAR(50) UNIQUE NOT NULL, -- e.g., "LAND-A-001", "BLDG-B-101"
    product_id INT NOT NULL,
    unit_label VARCHAR(100) NOT NULL, -- e.g., "House 1", "Plot A", "Block B Unit 5"
    unit_type ENUM('plot', 'house', 'apartment', 'block', 'unit') NOT NULL,
    status ENUM('available', 'allocated', 'reserved', 'maintenance') DEFAULT 'available',
    size VARCHAR(50), -- e.g., "500sqm", "3 Bedroom"
    price DECIMAL(15,2),
    features TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_unit_code (unit_code),
    INDEX idx_status (status),
    INDEX idx_product (product_id)
);

-- Allocations table
CREATE TABLE allocations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    allocation_number VARCHAR(30) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    product_id INT NOT NULL,
    unit_id INT NOT NULL,
    allocated_by INT NOT NULL,
    allocation_date DATE NOT NULL,
    allocation_amount DECIMAL(15,2) NOT NULL,
    payment_status ENUM('pending', 'partial', 'full', 'overdue') DEFAULT 'pending',
    allocation_status ENUM('active', 'cancelled', 'completed', 'transferred') DEFAULT 'active',
    next_payment_date DATE,
    total_payments DECIMAL(15,2) DEFAULT 0,
    balance DECIMAL(15,2) DEFAULT 0,
    notes TEXT,
    letter_generated BOOLEAN DEFAULT FALSE,
    letter_sent_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (unit_id) REFERENCES product_units(id),
    FOREIGN KEY (allocated_by) REFERENCES users(id),
    INDEX idx_allocation_number (allocation_number),
    INDEX idx_client (client_id),
    INDEX idx_product (product_id),
    INDEX idx_status (allocation_status)
);

-- Allocation Payments table
CREATE TABLE allocation_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    allocation_id INT NOT NULL,
    payment_reference VARCHAR(50) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method ENUM('bank_transfer', 'cash', 'cheque', 'online') DEFAULT 'bank_transfer',
    payment_date DATE NOT NULL,
    received_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (allocation_id) REFERENCES allocations(id),
    FOREIGN KEY (received_by) REFERENCES users(id),
    INDEX idx_allocation (allocation_id),
    INDEX idx_reference (payment_reference)
);

-- Allocation Letters table
CREATE TABLE allocation_letters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    allocation_id INT NOT NULL,
    letter_number VARCHAR(30) UNIQUE NOT NULL,
    letter_path VARCHAR(255), -- Path to generated PDF
    letter_content TEXT,
    email_sent BOOLEAN DEFAULT FALSE,
    email_sent_date DATETIME,
    email_to VARCHAR(255),
    email_cc VARCHAR(255),
    email_bcc VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (allocation_id) REFERENCES allocations(id),
    INDEX idx_letter_number (letter_number),
    INDEX idx_allocation (allocation_id)
);