-- =============================================================================
-- SoftShore Billing v2.0 - Complete MySQL 8 Database Schema
-- Multi-Tenant SaaS Billing, Integrated CRM & Full-Phase Double-Entry Accounting
-- Prepared for: SoftShore Technology
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Subscription Plans (Platform Level)
CREATE TABLE IF NOT EXISTS plans (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price_monthly DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    price_yearly DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    max_users INT NOT NULL DEFAULT 5,
    max_branches INT NOT NULL DEFAULT 1,
    max_invoices_month INT NOT NULL DEFAULT 500,
    features JSON NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tenants (Subscribing Companies)
CREATE TABLE IF NOT EXISTS tenants (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    subdomain VARCHAR(100) NOT NULL UNIQUE,
    custom_domain VARCHAR(150) NULL,
    plan_id VARCHAR(50) NOT NULL,
    status ENUM('active', 'trial', 'suspended', 'cancelled') DEFAULT 'active',
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Dhaka',
    tax_id VARCHAR(80) NULL,
    vat_rate DECIMAL(5, 2) NOT NULL DEFAULT 15.00,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    address TEXT NULL,
    logo_url VARCHAR(255) NULL,
    onboarding_completed BOOLEAN DEFAULT FALSE,
    settings JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_subdomain (subdomain),
    INDEX idx_tenant_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Branches / Cost Centers
CREATE TABLE IF NOT EXISTS branches (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    manager_id VARCHAR(50) NULL,
    is_main BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_branch_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Roles (RBAC)
CREATE TABLE IF NOT EXISTS roles (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NULL,
    name VARCHAR(80) NOT NULL,
    scope ENUM('platform', 'tenant', 'branch', 'self') NOT NULL DEFAULT 'tenant',
    permissions JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_role_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Users
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NULL,
    branch_id VARCHAR(50) NULL,
    role_id VARCHAR(50) NOT NULL,
    role_name VARCHAR(80) NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'invited', 'suspended') DEFAULT 'active',
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_tenant_email (tenant_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Customers
CREATE TABLE IF NOT EXISTS customers (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(150) NOT NULL,
    company VARCHAR(150) NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    address TEXT NULL,
    tax_id VARCHAR(80) NULL,
    tags JSON NULL,
    credit_limit DECIMAL(15, 2) DEFAULT 100000.00,
    advance_balance DECIMAL(15, 2) DEFAULT 0.00,
    payment_score INT DEFAULT 90,
    status ENUM('active', 'inactive', 'overdue_hold') DEFAULT 'active',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. CRM Leads & Pipeline
CREATE TABLE IF NOT EXISTS leads (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    name VARCHAR(150) NOT NULL,
    company VARCHAR(150) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    source VARCHAR(80) DEFAULT 'Website',
    stage ENUM('New', 'Contacted', 'Qualified', 'Won', 'Lost') DEFAULT 'New',
    estimated_value DECIMAL(15, 2) DEFAULT 0.00,
    probability INT DEFAULT 25,
    assigned_to VARCHAR(100) NULL,
    interested_product_id VARCHAR(50) NULL,
    notes TEXT NULL,
    converted_customer_id VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_tenant_stage (tenant_id, stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. CRM Interactions & Executive Tasks
CREATE TABLE IF NOT EXISTS crm_activities (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    entity_type ENUM('lead', 'customer') NOT NULL,
    entity_id VARCHAR(50) NOT NULL,
    activity_type ENUM('call', 'email', 'meeting', 'note', 'task') NOT NULL,
    subject VARCHAR(200) NOT NULL,
    details TEXT NULL,
    due_date DATE NULL,
    is_completed BOOLEAN DEFAULT FALSE,
    assigned_to VARCHAR(100) NULL,
    created_by VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_crm_tenant (tenant_id, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Chart of Accounts (COA - Auto-Seeded per Tenant)
CREATE TABLE IF NOT EXISTS accounts (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(150) NOT NULL,
    type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    subtype VARCHAR(80) NULL,
    parent_id VARCHAR(50) NULL,
    is_system BOOLEAN DEFAULT FALSE,
    control_key VARCHAR(50) NULL,
    opening_debit DECIMAL(15, 2) DEFAULT 0.00,
    opening_credit DECIMAL(15, 2) DEFAULT 0.00,
    status ENUM('active', 'archived') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_account_code (tenant_id, code),
    INDEX idx_account_tenant_type (tenant_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Products & Services
CREATE TABLE IF NOT EXISTS products (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    type ENUM('product', 'service') NOT NULL DEFAULT 'service',
    sku VARCHAR(60) NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NULL,
    description TEXT NULL,
    price DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    cost DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) NOT NULL DEFAULT 15.00,
    unit VARCHAR(30) DEFAULT 'unit',
    track_inventory BOOLEAN DEFAULT FALSE,
    stock_qty INT DEFAULT 0,
    low_stock_alert INT DEFAULT 5,
    revenue_account_code VARCHAR(20) DEFAULT '4100',
    cogs_account_code VARCHAR(20) DEFAULT '5000',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Subscriptions & Recurring Billing Cycles
CREATE TABLE IF NOT EXISTS subscriptions (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NOT NULL,
    product_id VARCHAR(50) NOT NULL,
    plan_name VARCHAR(150) NOT NULL,
    cycle ENUM('one_time', 'bi_weekly', 'monthly', 'quarterly', 'half_yearly', 'yearly', 'custom') NOT NULL DEFAULT 'monthly',
    custom_days INT NULL,
    start_date DATE NOT NULL,
    next_invoice_date DATE NOT NULL,
    qty DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    discount_type ENUM('percent', 'fixed') DEFAULT 'percent',
    discount_value DECIMAL(15, 2) DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) DEFAULT 15.00,
    auto_renew BOOLEAN DEFAULT TRUE,
    is_deferred_revenue BOOLEAN DEFAULT FALSE,
    deferred_months INT DEFAULT 1,
    status ENUM('active', 'paused', 'cancelled', 'expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sub_tenant_next (tenant_id, status, next_invoice_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Quotations / Estimates (Value-Add Module)
CREATE TABLE IF NOT EXISTS quotations (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NOT NULL,
    lead_id VARCHAR(50) NULL,
    quote_number VARCHAR(50) NOT NULL,
    issue_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    currency VARCHAR(10) DEFAULT 'BDT',
    items JSON NOT NULL,
    subtotal DECIMAL(15, 2) NOT NULL,
    discount DECIMAL(15, 2) DEFAULT 0.00,
    tax DECIMAL(15, 2) DEFAULT 0.00,
    total DECIMAL(15, 2) NOT NULL,
    status ENUM('Draft', 'Sent', 'Accepted', 'Converted', 'Declined') DEFAULT 'Sent',
    converted_invoice_id VARCHAR(50) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_quote_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Invoices & Multi-Line Items
CREATE TABLE IF NOT EXISTS invoices (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NOT NULL,
    subscription_id VARCHAR(50) NULL,
    number VARCHAR(60) NOT NULL,
    date DATE NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('Draft', 'Sent', 'Partially Paid', 'Paid', 'Overdue', 'Cancelled', 'Void') DEFAULT 'Sent',
    currency VARCHAR(10) DEFAULT 'BDT',
    exchange_rate DECIMAL(12, 4) DEFAULT 1.0000,
    subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    discount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    rounding_adjustment DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    total DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
     credited_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    terms TEXT NULL,
    je_id VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_invoice_tenant_status (tenant_id, status, due_date),
    INDEX idx_invoice_customer (tenant_id, customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id VARCHAR(50) PRIMARY KEY,
    invoice_id VARCHAR(50) NOT NULL,
    product_id VARCHAR(50) NULL,
    description VARCHAR(255) NOT NULL,
    qty DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    discount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) NOT NULL DEFAULT 15.00,
    tax_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    total DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    account_code VARCHAR(20) DEFAULT '4100',
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Payments
CREATE TABLE IF NOT EXISTS payments (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    invoice_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NOT NULL,
    receipt_number VARCHAR(60) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    bank_charge DECIMAL(15, 2) DEFAULT 0.00,
    method ENUM('Cash', 'Bank Transfer', 'Card', 'bKash', 'Nagad', 'SSLCommerz', 'Stripe', 'PayPal', 'Advance Wallet') NOT NULL DEFAULT 'Bank Transfer',
    deposit_account_code VARCHAR(20) DEFAULT '1010',
    reference VARCHAR(100) NULL,
    is_advance BOOLEAN DEFAULT FALSE,
    paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('Completed', 'Refunded', 'Pending') DEFAULT 'Completed',
    je_id VARCHAR(50) NULL,
    INDEX idx_payment_tenant (tenant_id, paid_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Credit Notes & Refunds
CREATE TABLE IF NOT EXISTS credit_notes (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    invoice_id VARCHAR(50) NOT NULL,
    customer_id VARCHAR(50) NOT NULL,
    cn_number VARCHAR(60) NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    tax_reversal DECIMAL(15, 2) DEFAULT 0.00,
    reason VARCHAR(255) NOT NULL,
    refund_mode ENUM('ar_adjustment', 'cash_refund', 'wallet_credit') DEFAULT 'ar_adjustment',
    status ENUM('Issued', 'Applied', 'Refunded') DEFAULT 'Applied',
    je_id VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cn_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Vendors & Bills / Expenses (AP & Expenditure Module)
CREATE TABLE IF NOT EXISTS vendors (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    tax_id VARCHAR(80) NULL,
    category VARCHAR(80) DEFAULT 'Supplier',
    balance_payable DECIMAL(15, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_vendor_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses_bills (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    vendor_id VARCHAR(50) NULL,
    voucher_number VARCHAR(60) NOT NULL,
    category ENUM('expense', 'inventory_purchase', 'payroll', 'vat_remittance', 'depreciation') DEFAULT 'expense',
    expense_account_code VARCHAR(20) NOT NULL,
    payment_account_code VARCHAR(20) NOT NULL,
    date DATE NOT NULL,
    due_date DATE NULL,
    amount DECIMAL(15, 2) NOT NULL,
    tax_amount DECIMAL(15, 2) DEFAULT 0.00,
    paid_amount DECIMAL(15, 2) DEFAULT 0.00,
    status ENUM('Paid', 'Unpaid', 'Partially Paid') DEFAULT 'Paid',
    description VARCHAR(255) NOT NULL,
    je_id VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_exp_tenant (tenant_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. Fixed Assets Register (Automated Depreciation)
CREATE TABLE IF NOT EXISTS fixed_assets (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    asset_code VARCHAR(40) NOT NULL,
    name VARCHAR(150) NOT NULL,
    purchase_date DATE NOT NULL,
    purchase_cost DECIMAL(15, 2) NOT NULL,
    salvage_value DECIMAL(15, 2) DEFAULT 0.00,
    useful_life_months INT NOT NULL DEFAULT 60,
    accumulated_depreciation DECIMAL(15, 2) DEFAULT 0.00,
    last_depreciation_date DATE NULL,
    status ENUM('Active', 'Fully Depreciated', 'Disposed') DEFAULT 'Active',
    INDEX idx_asset_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Fiscal Years & Monthly Accounting Periods
CREATE TABLE IF NOT EXISTS fiscal_years (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    name VARCHAR(80) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('open', 'closed') DEFAULT 'open',
    closed_at TIMESTAMP NULL,
    INDEX idx_fy_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_periods (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    fiscal_year_id VARCHAR(50) NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    name VARCHAR(40) NOT NULL,
    status ENUM('open', 'locked', 'closed') DEFAULT 'open',
    unlock_audit_note TEXT NULL,
    INDEX idx_period_tenant (tenant_id, year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Journal Entries (Double-Entry Header & Lines)
CREATE TABLE IF NOT EXISTS journal_entries (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    branch_id VARCHAR(50) NULL,
    je_number VARCHAR(60) NOT NULL,
    date DATE NOT NULL,
    narration VARCHAR(255) NOT NULL,
    source_module VARCHAR(60) NOT NULL DEFAULT 'Manual',
    event_type VARCHAR(80) NULL,
    reference_id VARCHAR(60) NULL,
    status ENUM('Draft', 'Queued', 'Approved', 'Posted', 'Reversed') DEFAULT 'Posted',
    reversal_of_je_id VARCHAR(50) NULL,
    reversed_by_je_id VARCHAR(50) NULL,
    total_debit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    total_credit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    created_by VARCHAR(100) NOT NULL,
    approved_by VARCHAR(100) NULL,
    posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_je_tenant_date (tenant_id, date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_lines (
    id VARCHAR(50) PRIMARY KEY,
    je_id VARCHAR(50) NOT NULL,
    tenant_id VARCHAR(50) NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    debit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    branch_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NULL,
    vendor_id VARCHAR(50) NULL,
    tax_code VARCHAR(30) NULL,
    memo VARCHAR(255) NULL,
    FOREIGN KEY (je_id) REFERENCES journal_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. General Ledger Entries (Immutable Posted Ledger Lines)
CREATE TABLE IF NOT EXISTS ledger_entries (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    je_id VARCHAR(50) NOT NULL,
    je_number VARCHAR(60) NOT NULL,
    line_id VARCHAR(50) NOT NULL,
    date DATE NOT NULL,
    narration VARCHAR(255) NOT NULL,
    debit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    balance_after DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    branch_id VARCHAR(50) NULL,
    customer_id VARCHAR(50) NULL,
    posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gl_tenant_account (tenant_id, account_code, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. Notification Templates & Delivery Logs
CREATE TABLE IF NOT EXISTS notification_templates (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    event VARCHAR(80) NOT NULL,
    label VARCHAR(120) NOT NULL,
    channel ENUM('email', 'sms', 'both', 'in_app') DEFAULT 'both',
    days_offset INT DEFAULT 0,
    email_subject VARCHAR(200) NULL,
    email_body TEXT NULL,
    sms_body TEXT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    INDEX idx_nt_tenant (tenant_id, event)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_logs (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    channel ENUM('email', 'sms', 'in_app') NOT NULL,
    recipient VARCHAR(150) NOT NULL,
    customer_name VARCHAR(150) NULL,
    event VARCHAR(80) NOT NULL,
    subject VARCHAR(200) NULL,
    message TEXT NOT NULL,
    gateway_response VARCHAR(255) NULL,
    status ENUM('sent', 'delivered', 'queued_dnd', 'failed') DEFAULT 'sent',
    retry_count INT DEFAULT 0,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nl_tenant (tenant_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. Immutable Audit Logs
CREATE TABLE IF NOT EXISTS audit_logs (
    id VARCHAR(50) PRIMARY KEY,
    tenant_id VARCHAR(50) NULL,
    user_id VARCHAR(50) NULL,
    user_name VARCHAR(120) NOT NULL,
    user_role VARCHAR(80) NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity VARCHAR(80) NOT NULL,
    entity_id VARCHAR(80) NULL,
    summary VARCHAR(255) NOT NULL,
    before_state JSON NULL,
    after_state JSON NULL,
    ip_address VARCHAR(45) DEFAULT '127.0.0.1',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;