-- Clients System Database Tables

-- Enhanced Clients Table
CREATE TABLE IF NOT EXISTS clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    company VARCHAR(100),
    address TEXT,
    city VARCHAR(50),
    country VARCHAR(50),
    purchase_code VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    profile_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    
    INDEX idx_purchase_code (purchase_code),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

-- Client Projects
CREATE TABLE IF NOT EXISTS client_projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    project_name VARCHAR(200) NOT NULL,
    description TEXT,
    project_type VARCHAR(50),
    status ENUM('pending', 'in_progress', 'completed', 'on_hold', 'cancelled') DEFAULT 'pending',
    start_date DATE,
    end_date DATE,
    estimated_budget DECIMAL(10, 2),
    actual_cost DECIMAL(10, 2),
    progress INT DEFAULT 0,
    assigned_to INT,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_status (status)
);

-- Client Payments
CREATE TABLE IF NOT EXISTS client_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    project_id INT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'credit_card', 'paypal', 'stripe', 'other') DEFAULT 'bank_transfer',
    payment_status ENUM('pending', 'paid', 'partial', 'failed', 'refunded') DEFAULT 'pending',
    payment_date DATE,
    due_date DATE,
    description TEXT,
    receipt_url VARCHAR(255),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES client_projects(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_invoice (invoice_number),
    INDEX idx_status (payment_status)
);

-- Client Quotations
CREATE TABLE IF NOT EXISTS client_quotations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    quotation_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    amount DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('draft', 'sent', 'accepted', 'rejected', 'expired') DEFAULT 'draft',
    valid_until DATE,
    terms_conditions TEXT,
    notes TEXT,
    pdf_url VARCHAR(255),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_quotation (quotation_number),
    INDEX idx_status (status)
);

-- Quotation Items
CREATE TABLE IF NOT EXISTS quotation_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quotation_id INT NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    description TEXT,
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    
    FOREIGN KEY (quotation_id) REFERENCES client_quotations(id) ON DELETE CASCADE,
    INDEX idx_quotation_id (quotation_id)
);

-- Client Proposals
CREATE TABLE IF NOT EXISTS client_proposals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    proposal_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    scope_of_work TEXT,
    deliverables TEXT,
    timeline VARCHAR(100),
    budget DECIMAL(10, 2),
    status ENUM('draft', 'sent', 'under_review', 'accepted', 'rejected') DEFAULT 'draft',
    pdf_url VARCHAR(255),
    sent_date DATE,
    response_date DATE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_proposal (proposal_number),
    INDEX idx_status (status)
);

-- Client Support Tickets
CREATE TABLE IF NOT EXISTS client_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    ticket_number VARCHAR(50) UNIQUE NOT NULL,
    subject VARCHAR(200) NOT NULL,
    description TEXT,
    category ENUM('technical', 'billing', 'general', 'feature_request', 'bug_report', 'other') DEFAULT 'general',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'pending', 'resolved', 'closed') DEFAULT 'open',
    assigned_to INT,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_ticket (ticket_number),
    INDEX idx_status (status)
);

-- Ticket Messages
CREATE TABLE IF NOT EXISTS ticket_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    sender_type ENUM('client', 'staff') NOT NULL,
    sender_id INT NOT NULL,
    message TEXT NOT NULL,
    attachment_url VARCHAR(255),
    is_internal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES client_tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket_id (ticket_id)
);

-- Client Documents/Receipts
CREATE TABLE IF NOT EXISTS client_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    project_id INT,
    payment_id INT,
    document_type ENUM('receipt', 'invoice', 'contract', 'agreement', 'report', 'other') DEFAULT 'other',
    title VARCHAR(200) NOT NULL,
    description TEXT,
    file_url VARCHAR(255) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    uploaded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES client_projects(id) ON DELETE SET NULL,
    FOREIGN KEY (payment_id) REFERENCES client_payments(id) ON DELETE SET NULL,
    FOREIGN KEY (uploaded_by) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_type (document_type)
);

-- Project Updates/Activity Log
CREATE TABLE IF NOT EXISTS project_updates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    update_type ENUM('status_change', 'progress_update', 'milestone', 'comment', 'file_upload') DEFAULT 'comment',
    title VARCHAR(200),
    description TEXT,
    progress_percentage INT,
    created_by INT,
    is_visible_to_client BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (project_id) REFERENCES client_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES staff_members(id) ON DELETE SET NULL,
    INDEX idx_project_id (project_id)
);
