-- ============================================================
-- CashBook ERP - Database Schema for cPanel (MySQL/MariaDB)
-- MySQL Database for Ghanaian SME Bookkeeping
--
-- This schema is compatible with MySQL 5.7+ and MariaDB 10.2+
-- Uses UUID() function for generating UUIDs
-- ============================================================

-- ============================================================
-- 1. CORE TABLES - Tenants, Companies, Users, Settings
-- ============================================================

CREATE TABLE IF NOT EXISTS tenants (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255),
    plan VARCHAR(50) DEFAULT 'free',
    max_businesses INT DEFAULT 1,
    max_users INT DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    settings TEXT DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS companies (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    trading_name VARCHAR(255),
    tin_number VARCHAR(20),
    business_registration VARCHAR(50),
    vat_registered BOOLEAN DEFAULT FALSE,
    vat_number VARCHAR(20),
    nhil_registered BOOLEAN DEFAULT FALSE,
    getfund_registered BOOLEAN DEFAULT FALSE,
    covid_levy_registered BOOLEAN DEFAULT FALSE,
    address TEXT,
    city VARCHAR(100) DEFAULT 'Accra',
    region VARCHAR(100),
    digital_address VARCHAR(20),
    phone VARCHAR(20),
    email VARCHAR(255),
    website VARCHAR(255),
    logo_url TEXT,
    currency VARCHAR(3) DEFAULT 'GHS',
    supported_currencies VARCHAR(100) DEFAULT '',
    fiscal_year_start INT DEFAULT 1,
    default_profit_margin DECIMAL(5,2) DEFAULT 0,
    industry VARCHAR(100),
    tenant_id CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS users (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    permissions TEXT DEFAULT '{}',
    role VARCHAR(50) DEFAULT 'staff',
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_businesses (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    user_id CHAR(36) NOT NULL,
    company_id CHAR(36) NOT NULL,
    role VARCHAR(50) DEFAULT 'staff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- ============================================================
-- 2. ACCOUNTING - Charts, Accounts, Journals, Ledgers
-- ============================================================

CREATE TABLE IF NOT EXISTS chart_of_accounts (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50),
    type VARCHAR(20),
    balance_type VARCHAR(10),
    parent_id CHAR(36),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(company_id, code),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES chart_of_accounts(id)
);

CREATE TABLE IF NOT EXISTS journal_entries (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    entry_number VARCHAR(50) NOT NULL,
    entry_date DATE NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    post_date TIMESTAMP NULL,
    posted_by CHAR(36),
    created_by CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(company_id, entry_number),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (posted_by) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS journal_lines (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    journal_id CHAR(36) NOT NULL,
    account_id CHAR(36) NOT NULL,
    line_number INT,
    debit DECIMAL(15,2) DEFAULT 0,
    credit DECIMAL(15,2) DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id)
);

-- ============================================================
-- 3. INVOICING
-- ============================================================

CREATE TABLE IF NOT EXISTS invoices (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    customer_id CHAR(36),
    invoice_date DATE NOT NULL,
    due_date DATE,
    amount_subtotal DECIMAL(15,2) DEFAULT 0,
    amount_tax DECIMAL(15,2) DEFAULT 0,
    amount_total DECIMAL(15,2) DEFAULT 0,
    paid_amount DECIMAL(15,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'draft',
    notes TEXT,
    created_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(company_id, invoice_number),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS payments (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    invoice_id CHAR(36),
    payment_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(50),
    reference VARCHAR(100),
    notes TEXT,
    created_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ============================================================
-- 4. POS (Point of Sale)
-- ============================================================

CREATE TABLE IF NOT EXISTS pos_terminals (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    terminal_name VARCHAR(100),
    location VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS pos_sessions (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    terminal_id CHAR(36),
    user_id CHAR(36) NOT NULL,
    opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    opening_balance DECIMAL(15,2) DEFAULT 0,
    closing_balance DECIMAL(15,2),
    total_sales DECIMAL(15,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'open',
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (terminal_id) REFERENCES pos_terminals(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS pos_sales (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    session_id CHAR(36) NOT NULL,
    sale_number VARCHAR(50),
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount_subtotal DECIMAL(15,2) DEFAULT 0,
    amount_tax DECIMAL(15,2) DEFAULT 0,
    amount_total DECIMAL(15,2),
    payment_method VARCHAR(50),
    status VARCHAR(20) DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES pos_sessions(id)
);

-- ============================================================
-- 5. PRODUCTS & INVENTORY
-- ============================================================

CREATE TABLE IF NOT EXISTS product_categories (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    parent_id CHAR(36),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(company_id, name),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES product_categories(id)
);

CREATE TABLE IF NOT EXISTS products (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    category_id CHAR(36),
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50),
    barcode VARCHAR(100),
    description TEXT,
    unit_price DECIMAL(15,2),
    cost_price DECIMAL(15,2),
    quantity INT DEFAULT 0,
    low_stock_threshold INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(company_id, sku),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES product_categories(id)
);

CREATE TABLE IF NOT EXISTS inventory_movements (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    product_id CHAR(36) NOT NULL,
    movement_type VARCHAR(50),
    quantity INT,
    reference VARCHAR(100),
    notes TEXT,
    created_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ============================================================
-- 6. PAYROLL
-- ============================================================

CREATE TABLE IF NOT EXISTS employees (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    date_of_birth DATE,
    id_number VARCHAR(50),
    ssnit_number VARCHAR(50),
    bank_account VARCHAR(50),
    basic_salary DECIMAL(15,2),
    employment_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payroll_periods (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    name VARCHAR(100),
    period_start DATE,
    period_end DATE,
    pay_date DATE,
    status VARCHAR(20) DEFAULT 'open',
    processed_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS payslips (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    period_id CHAR(36) NOT NULL,
    employee_id CHAR(36) NOT NULL,
    basic_salary DECIMAL(15,2),
    gross_salary DECIMAL(15,2),
    paye_amount DECIMAL(15,2),
    ssnit_amount DECIMAL(15,2),
    net_amount DECIMAL(15,2),
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (period_id) REFERENCES payroll_periods(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- ============================================================
-- 7. TAX COMPLIANCE
-- ============================================================

CREATE TABLE IF NOT EXISTS tax_rates (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    tax_type VARCHAR(50),
    rate DECIMAL(5,2),
    description TEXT,
    effective_date DATE,
    end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tax_filings (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    filing_type VARCHAR(50),
    filing_period VARCHAR(50),
    amount DECIMAL(15,2),
    status VARCHAR(20) DEFAULT 'pending',
    filing_date DATE,
    due_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- ============================================================
-- 8. ASSETS
-- ============================================================

CREATE TABLE IF NOT EXISTS fixed_assets (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    asset_code VARCHAR(50),
    asset_name VARCHAR(255),
    category VARCHAR(100),
    acquisition_date DATE,
    cost DECIMAL(15,2),
    residual_value DECIMAL(15,2),
    useful_life_years INT,
    depreciation_method VARCHAR(50),
    accumulated_depreciation DECIMAL(15,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(company_id, asset_code),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- ============================================================
-- 9. CONTACTS & RELATIONSHIPS
-- ============================================================

CREATE TABLE IF NOT EXISTS contacts (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    contact_type VARCHAR(50),
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    tax_id VARCHAR(50),
    address TEXT,
    city VARCHAR(100),
    region VARCHAR(100),
    digital_address VARCHAR(20),
    country VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- ============================================================
-- 10. TRACKING & AUDIT
-- ============================================================

CREATE TABLE IF NOT EXISTS audit_log (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    user_id CHAR(36),
    action VARCHAR(50),
    entity_type VARCHAR(50),
    entity_id CHAR(36),
    old_values TEXT,
    new_values TEXT,
    ip_address VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS file_attachments (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    company_id CHAR(36) NOT NULL,
    entity_type VARCHAR(50),
    entity_id CHAR(36),
    file_name VARCHAR(255),
    original_name VARCHAR(255),
    file_size INT,
    mime_type VARCHAR(100),
    file_path TEXT,
    uploaded_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ============================================================
-- INDEXES for Performance
-- ============================================================

CREATE INDEX idx_companies_tenant_id ON companies(tenant_id);
CREATE INDEX idx_users_company_id ON users(company_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_user_businesses_user_id ON user_businesses(user_id);
CREATE INDEX idx_user_businesses_company_id ON user_businesses(company_id);
CREATE INDEX idx_chart_of_accounts_company_id ON chart_of_accounts(company_id);
CREATE INDEX idx_journal_entries_company_id ON journal_entries(company_id);
CREATE INDEX idx_journal_lines_journal_id ON journal_lines(journal_id);
CREATE INDEX idx_invoices_company_id ON invoices(company_id);
CREATE INDEX idx_invoices_number ON invoices(company_id, invoice_number);
CREATE INDEX idx_payments_invoice_id ON payments(invoice_id);
CREATE INDEX idx_pos_sessions_user_id ON pos_sessions(user_id);
CREATE INDEX idx_pos_sales_session_id ON pos_sales(session_id);
CREATE INDEX idx_products_company_id ON products(company_id);
CREATE INDEX idx_employees_company_id ON employees(company_id);
CREATE INDEX idx_payroll_periods_company_id ON payroll_periods(company_id);
CREATE INDEX idx_payslips_period_id ON payslips(period_id);
CREATE INDEX idx_audit_log_company_id ON audit_log(company_id);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);
CREATE INDEX idx_file_attachments_entity ON file_attachments(entity_type, entity_id);
