USE knowledg_ap_lite;

CREATE TABLE vendors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_name VARCHAR(255) NOT NULL,
    vendor_code VARCHAR(100) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vendor_code (vendor_code)
);

CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_code VARCHAR(100) NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    project_type ENUM('residential', 'commercial') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_projects_code (project_code)
);

CREATE TABLE buildings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    building_code VARCHAR(100) NOT NULL,
    building_name VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_buildings_project
        FOREIGN KEY (project_id) REFERENCES projects(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE KEY uq_building_per_project (project_id, building_code)
);

CREATE TABLE units (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    building_id BIGINT UNSIGNED NOT NULL,
    unit_code VARCHAR(100) NOT NULL,
    unit_name VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_units_building
        FOREIGN KEY (building_id) REFERENCES buildings(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE KEY uq_unit_per_building (building_id, unit_code)
);

CREATE TABLE invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_id BIGINT UNSIGNED NOT NULL,
    vendor_invoice_number VARCHAR(150) DEFAULT NULL,
    internal_reference_number VARCHAR(150) NOT NULL,
    invoice_date DATE NOT NULL,
    received_date DATE DEFAULT NULL,
    invoice_amount DECIMAL(15,2) NOT NULL,
    description TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    status ENUM('draft', 'submitted', 'approved', 'partially_paid', 'paid', 'void') NOT NULL DEFAULT 'draft',
    vendor_invoice_number_normalized VARCHAR(150) DEFAULT NULL,
    internal_reference_number_normalized VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_invoices_vendor
        FOREIGN KEY (vendor_id) REFERENCES vendors(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    UNIQUE KEY uq_internal_reference_number (internal_reference_number_normalized),
    UNIQUE KEY uq_vendor_invoice_per_vendor (vendor_id, vendor_invoice_number_normalized),
    KEY idx_invoice_date (invoice_date),
    KEY idx_invoice_status (status)
);

CREATE TABLE invoice_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    file_size BIGINT UNSIGNED DEFAULT NULL,
    uploaded_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_invoice_attachments_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices(id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    KEY idx_invoice_attachments_invoice (invoice_id)
);

CREATE TABLE invoice_allocations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    project_id BIGINT UNSIGNED NOT NULL,
    building_id BIGINT UNSIGNED DEFAULT NULL,
    unit_id BIGINT UNSIGNED DEFAULT NULL,
    allocated_amount DECIMAL(15,2) NOT NULL,

    CONSTRAINT fk_invoice_allocations_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices(id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_invoice_allocations_project
        FOREIGN KEY (project_id) REFERENCES projects(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_invoice_allocations_building
        FOREIGN KEY (building_id) REFERENCES buildings(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_invoice_allocations_unit
        FOREIGN KEY (unit_id) REFERENCES units(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    KEY idx_alloc_invoice (invoice_id),
    KEY idx_alloc_project (project_id),
    KEY idx_alloc_building (building_id),
    KEY idx_alloc_unit (unit_id)
);

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    check_number VARCHAR(100) NOT NULL,
    check_date DATE NOT NULL,
    payee_name VARCHAR(255) NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    notes TEXT DEFAULT NULL,
    voided_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_check_number (check_number)
);

CREATE TABLE payment_allocations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NOT NULL,
    allocated_amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_payment_allocations_payment
        FOREIGN KEY (payment_id) REFERENCES payments(id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_payment_allocations_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    KEY idx_payment_allocations_payment (payment_id),
    KEY idx_payment_allocations_invoice (invoice_id)
);