-- ═══════════════════════════════════════════════════════════════════════
--  LedgerPro — Professional Accounting Platform
--  schema.sql — Complete Database Schema
-- ═══════════════════════════════════════════════════════════════════════
--
--  Engine : InnoDB (required for foreign keys + transactions)
--  Charset: utf8mb4 (full Unicode including emoji)
--  Collation: utf8mb4_unicode_ci (accent/case-insensitive sorting)
--
--  Run this file once to set up the database:
--    mysql -u root -p < schema.sql
-- ═══════════════════════════════════════════════════════════════════════

CREATE DATABASE IF NOT EXISTS `ledgerpro`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `ledgerpro`;


-- ─────────────────────────────────────────────────────────────────────
--  2. CLIENTS — Business or individual client profiles
-- ─────────────────────────────────────────────────────────────────────
--  Stores billing details, tax IDs, and contact information.
--  A client can optionally have a portal user account via users.client_id.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `clients` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `company_name`    VARCHAR(200)    NOT NULL,
    `contact_name`    VARCHAR(120)    NOT NULL,
    `email`           VARCHAR(255)    NOT NULL,
    `phone`           VARCHAR(30)     NULL DEFAULT NULL,
    `tax_id`          VARCHAR(50)     NULL DEFAULT NULL,
    `address_line1`   VARCHAR(255)    NULL DEFAULT NULL,
    `address_line2`   VARCHAR(255)    NULL DEFAULT NULL,
    `city`            VARCHAR(100)    NULL DEFAULT NULL,
    `state`           VARCHAR(100)    NULL DEFAULT NULL,
    `postal_code`     VARCHAR(20)     NULL DEFAULT NULL,
    `country`         VARCHAR(80)     NOT NULL DEFAULT 'US',
    `notes`           TEXT            NULL,
    `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_clients_company` (`company_name`),
    KEY `idx_clients_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────────────
--  3. INVOICES — Header-level invoice records
-- ─────────────────────────────────────────────────────────────────────
--  Each invoice belongs to one client.
--  Subtotal, tax, and total are stored denormalized for fast reads;
--  they get recalculated on every line-item change.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `invoices` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `invoice_number`  VARCHAR(30)     NOT NULL,
    `client_id`       INT UNSIGNED    NOT NULL,
    `status`          ENUM('draft','sent','paid','overdue','cancelled')
                                      NOT NULL DEFAULT 'draft',
    `issue_date`      DATE            NOT NULL,
    `due_date`        DATE            NOT NULL,
    `subtotal`        DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `tax_rate`        DECIMAL(5,2)    NOT NULL DEFAULT 0.00,
    `tax_amount`      DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `discount_amount` DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `total`           DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `amount_paid`     DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `balance_due`     DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `notes`           TEXT            NULL,
    `terms`           TEXT            NULL,
    `created_by`      INT UNSIGNED    NULL DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_invoices_number` (`invoice_number`),
    KEY `idx_invoices_client` (`client_id`),
    KEY `idx_invoices_status` (`status`),
    KEY `idx_invoices_due` (`due_date`),
    KEY `idx_invoices_created_by` (`created_by`),

    CONSTRAINT `fk_invoices_client`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT `fk_invoices_creator`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────────────
--  4. INVOICE_ITEMS — Line items belonging to an invoice
-- ─────────────────────────────────────────────────────────────────────
--  Each row is one billable line on an invoice.
--  `line_total` = quantity × unit_price (stored for fast reads).
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `invoice_items` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `invoice_id`      INT UNSIGNED    NOT NULL,
    `description`     VARCHAR(500)    NOT NULL,
    `quantity`         DECIMAL(10,2)   NOT NULL DEFAULT 1.00,
    `unit_price`      DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `line_total`      DECIMAL(14,2)   NOT NULL DEFAULT 0.00,
    `sort_order`      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_items_invoice` (`invoice_id`),

    CONSTRAINT `fk_items_invoice`
        FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────────────
--  5. PAYMENTS — Payments received against invoices
-- ─────────────────────────────────────────────────────────────────────
--  Supports partial payments. An invoice can have many payments.
--  `method` tracks how the money was received.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `payments` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `invoice_id`      INT UNSIGNED    NOT NULL,
    `client_id`       INT UNSIGNED    NOT NULL,
    `amount`          DECIMAL(14,2)   NOT NULL,
    `payment_date`    DATE            NOT NULL,
    `method`          ENUM('cash','bank_transfer','check','credit_card','paypal','other')
                                      NOT NULL DEFAULT 'bank_transfer',
    `reference`       VARCHAR(100)    NULL DEFAULT NULL,
    `notes`           TEXT            NULL,
    `recorded_by`     INT UNSIGNED    NULL DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_payments_invoice` (`invoice_id`),
    KEY `idx_payments_client` (`client_id`),
    KEY `idx_payments_date` (`payment_date`),

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

    CONSTRAINT `fk_payments_client`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT `fk_payments_recorder`
        FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────────────
--  6. INCOME — Non-invoice revenue entries
-- ─────────────────────────────────────────────────────────────────────
--  For revenue that doesn't come through the invoicing system,
--  e.g. interest income, refunds received, consulting retainers.
--  Optionally linked to a client.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `income` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`       INT UNSIGNED    NULL DEFAULT NULL,
    `category`        VARCHAR(100)    NOT NULL,
    `description`     VARCHAR(500)    NOT NULL,
    `amount`          DECIMAL(14,2)   NOT NULL,
    `income_date`     DATE            NOT NULL,
    `reference`       VARCHAR(100)    NULL DEFAULT NULL,
    `notes`           TEXT            NULL,
    `recorded_by`     INT UNSIGNED    NULL DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_income_client` (`client_id`),
    KEY `idx_income_date` (`income_date`),
    KEY `idx_income_category` (`category`),

    CONSTRAINT `fk_income_client`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT `fk_income_recorder`
        FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────────────
--  7. EXPENSES — Business expense tracking
-- ─────────────────────────────────────────────────────────────────────
--  Every business outflow is recorded here.
--  Optionally linked to a client for cost-center reporting.
--  `receipt_path` stores a relative path to the uploaded scan/photo.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `expenses` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`       INT UNSIGNED    NULL DEFAULT NULL,
    `category`        VARCHAR(100)    NOT NULL,
    `description`     VARCHAR(500)    NOT NULL,
    `amount`          DECIMAL(14,2)   NOT NULL,
    `expense_date`    DATE            NOT NULL,
    `vendor`          VARCHAR(200)    NULL DEFAULT NULL,
    `reference`       VARCHAR(100)    NULL DEFAULT NULL,
    `receipt_path`    VARCHAR(500)    NULL DEFAULT NULL,
    `is_billable`     TINYINT(1)      NOT NULL DEFAULT 0,
    `notes`           TEXT            NULL,
    `recorded_by`     INT UNSIGNED    NULL DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_expenses_client` (`client_id`),
    KEY `idx_expenses_date` (`expense_date`),
    KEY `idx_expenses_category` (`category`),
    KEY `idx_expenses_billable` (`is_billable`),

    CONSTRAINT `fk_expenses_client`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT `fk_expenses_recorder`
        FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
--  SAMPLE CLIENT — For demonstration / testing
-- ═══════════════════════════════════════════════════════════════════════

INSERT INTO `clients` (`company_name`, `contact_name`, `email`, `phone`, `tax_id`,
                        `address_line1`, `city`, `state`, `postal_code`, `country`)
VALUES (
    'Acme Industries',
    'John Doe',
    'john@acme.com',
    '+1 555-0100',
    'US-12-3456789',
    '742 Evergreen Terrace',
    'Springfield',
    'IL',
    '62704',
    'US'
);
