-- ============================================================
-- KopierSFlow ERP — 002_accounting_gaap.sql
-- Importar SEGUNDO en phpMyAdmin
-- ============================================================

CREATE TABLE chart_of_accounts (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    code            VARCHAR(20)  NOT NULL,
    name            VARCHAR(200) NOT NULL,
    type            ENUM('asset','liability','equity','revenue','cogs','expense') NOT NULL,
    subtype         VARCHAR(60),
    parent_id       BIGINT UNSIGNED NULL,
    is_header       TINYINT(1)   DEFAULT 0,
    normal_balance  ENUM('debit','credit') NOT NULL,
    is_bank_account TINYINT(1)   DEFAULT 0,
    is_system       TINYINT(1)   DEFAULT 0,
    is_active       TINYINT(1)   DEFAULT 1,
    description     TEXT,
    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 (parent_id)  REFERENCES chart_of_accounts(id) ON DELETE SET NULL,
    UNIQUE KEY uq_company_code (company_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fiscal_years (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(60)  NOT NULL,
    start_date  DATE         NOT NULL,
    end_date    DATE         NOT NULL,
    status      ENUM('open','closed') DEFAULT 'open',
    closed_by   BIGINT UNSIGNED,
    closed_at   TIMESTAMP NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE accounting_periods (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    fiscal_year_id BIGINT UNSIGNED NOT NULL,
    name           VARCHAR(30) NOT NULL,
    start_date     DATE        NOT NULL,
    end_date       DATE        NOT NULL,
    status         ENUM('open','closed') DEFAULT 'open',
    closed_by      BIGINT UNSIGNED,
    closed_at      TIMESTAMP NULL,
    FOREIGN KEY (company_id)     REFERENCES companies(id)    ON DELETE CASCADE,
    FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE CASCADE,
    INDEX idx_dates (company_id, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE journal_entries (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    period_id      BIGINT UNSIGNED NOT NULL,
    entry_number   VARCHAR(30)  NOT NULL,
    entry_date     DATE         NOT NULL,
    type           ENUM('automatic','manual','adjusting','closing') DEFAULT 'automatic',
    source_module  VARCHAR(60),
    source_id      BIGINT UNSIGNED,
    memo           TEXT,
    total_debit    DECIMAL(18,4) DEFAULT 0,
    total_credit   DECIMAL(18,4) DEFAULT 0,
    status         ENUM('draft','posted','reversed') DEFAULT 'posted',
    reversed_by    BIGINT UNSIGNED,
    created_by     BIGINT UNSIGNED,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id)        ON DELETE CASCADE,
    FOREIGN KEY (period_id)  REFERENCES accounting_periods(id),
    UNIQUE KEY uq_entry_number (company_id, entry_number),
    INDEX idx_date   (company_id, entry_date),
    INDEX idx_source (source_module, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE journal_entry_lines (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    company_id       BIGINT UNSIGNED NOT NULL,
    account_id       BIGINT UNSIGNED NOT NULL,
    debit            DECIMAL(18,4) DEFAULT 0,
    credit           DECIMAL(18,4) DEFAULT 0,
    memo             VARCHAR(500),
    entity_type      VARCHAR(60),
    entity_id        BIGINT UNSIGNED,
    line_order       TINYINT UNSIGNED DEFAULT 0,
    FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id)  ON DELETE CASCADE,
    FOREIGN KEY (account_id)       REFERENCES chart_of_accounts(id),
    INDEX idx_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ar_invoices (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    invoice_id        BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NOT NULL,
    invoice_date      DATE         NOT NULL,
    due_date          DATE         NOT NULL,
    amount            DECIMAL(18,4) NOT NULL,
    tax_amount        DECIMAL(18,4) DEFAULT 0,
    amount_paid       DECIMAL(18,4) DEFAULT 0,
    status            ENUM('open','partial','paid','overdue','void') DEFAULT 'open',
    journal_entry_id  BIGINT UNSIGNED,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_customer (company_id, customer_id, status),
    INDEX idx_due      (company_id, due_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ar_payments (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    customer_id      BIGINT UNSIGNED NOT NULL,
    payment_date     DATE           NOT NULL,
    amount           DECIMAL(18,4)  NOT NULL,
    payment_method   ENUM('check','cash','ach','stripe','paypal','square','wire','other'),
    reference        VARCHAR(100),
    bank_account_id  BIGINT UNSIGNED,
    merchant_fee     DECIMAL(18,4) DEFAULT 0,
    notes            TEXT,
    journal_entry_id BIGINT UNSIGNED,
    created_by       BIGINT UNSIGNED,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_allocations (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    payment_id     BIGINT UNSIGNED NOT NULL,
    ar_invoice_id  BIGINT UNSIGNED NOT NULL,
    amount         DECIMAL(18,4)  NOT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ap_bills (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    vendor_id         BIGINT UNSIGNED NOT NULL,
    bill_number       VARCHAR(60),
    bill_date         DATE          NOT NULL,
    due_date          DATE          NOT NULL,
    amount            DECIMAL(18,4) NOT NULL,
    tax_amount        DECIMAL(18,4) DEFAULT 0,
    amount_paid       DECIMAL(18,4) DEFAULT 0,
    status            ENUM('open','partial','paid','overdue','void') DEFAULT 'open',
    purchase_order_id BIGINT UNSIGNED,
    journal_entry_id  BIGINT UNSIGNED,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_vendor (company_id, vendor_id, status),
    INDEX idx_due    (company_id, due_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ap_payments (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    vendor_id        BIGINT UNSIGNED NOT NULL,
    payment_date     DATE           NOT NULL,
    amount           DECIMAL(18,4)  NOT NULL,
    payment_method   ENUM('check','ach','wire','other'),
    check_number     VARCHAR(30),
    bank_account_id  BIGINT UNSIGNED,
    notes            TEXT,
    journal_entry_id BIGINT UNSIGNED,
    created_by       BIGINT UNSIGNED,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bank_accounts (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    account_id     BIGINT UNSIGNED NOT NULL  COMMENT 'chart_of_accounts.id',
    bank_name      VARCHAR(100)   NOT NULL,
    account_name   VARCHAR(150)   NOT NULL,
    account_number VARCHAR(30)    COMMENT 'Últimos 4 dígitos solamente',
    routing_number VARCHAR(20),
    account_type   ENUM('checking','savings','credit_card') DEFAULT 'checking',
    currency       CHAR(3) DEFAULT 'USD',
    is_active      TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bank_transactions (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    bank_account_id  BIGINT UNSIGNED NOT NULL,
    transaction_date DATE           NOT NULL,
    amount           DECIMAL(18,4)  NOT NULL  COMMENT 'Positivo=crédito, Negativo=débito',
    description      VARCHAR(500),
    reference        VARCHAR(100),
    is_reconciled    TINYINT(1) DEFAULT 0,
    reconciled_at    TIMESTAMP NULL,
    journal_entry_id BIGINT UNSIGNED,
    imported_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)      REFERENCES companies(id)     ON DELETE CASCADE,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id),
    INDEX idx_date (company_id, bank_account_id, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bank_reconciliations (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id         BIGINT UNSIGNED NOT NULL,
    bank_account_id    BIGINT UNSIGNED NOT NULL,
    period_end_date    DATE           NOT NULL,
    statement_balance  DECIMAL(18,4)  NOT NULL,
    book_balance       DECIMAL(18,4)  NOT NULL,
    reconciled_balance DECIMAL(18,4),
    status             ENUM('in_progress','completed') DEFAULT 'in_progress',
    completed_by       BIGINT UNSIGNED,
    completed_at       TIMESTAMP NULL,
    notes              TEXT,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)      REFERENCES companies(id)      ON DELETE CASCADE,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tax_rates (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED NOT NULL,
    name         VARCHAR(100)   NOT NULL,
    state        CHAR(2)        NOT NULL,
    county       VARCHAR(100),
    city         VARCHAR(100),
    rate         DECIMAL(8,5)   NOT NULL  COMMENT 'Ej: 0.08250 = 8.25%',
    is_active    TINYINT(1) DEFAULT 1,
    nexus        TINYINT(1) DEFAULT 0     COMMENT 'Nexo económico establecido',
    effective_from DATE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_state (company_id, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sales_tax_returns (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    BIGINT UNSIGNED NOT NULL,
    state         CHAR(2)        NOT NULL,
    period_start  DATE           NOT NULL,
    period_end    DATE           NOT NULL,
    gross_sales   DECIMAL(18,4)  DEFAULT 0,
    taxable_sales DECIMAL(18,4)  DEFAULT 0,
    tax_collected DECIMAL(18,4)  DEFAULT 0,
    status        ENUM('draft','filed','paid') DEFAULT 'draft',
    filed_at      TIMESTAMP NULL,
    paid_at       TIMESTAMP NULL,
    notes         TEXT,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;