CREATE DATABASE IF NOT EXISTS contabilidade_core
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE contabilidade_core;

CREATE TABLE companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    tax_number VARCHAR(60) NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(120) NULL,
    base_currency CHAR(3) NOT NULL DEFAULT 'MZN',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','accountant') NOT NULL DEFAULT 'accountant',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE fiscal_years (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(80) NOT NULL,
    starts_on DATE NOT NULL,
    ends_on DATE NOT NULL,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_fiscal_year_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT chk_fiscal_period CHECK (starts_on <= ends_on)
) ENGINE=InnoDB;

CREATE TABLE accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(160) NOT NULL,
    nature ENUM('asset','liability','equity','income','expense') NOT NULL,
    account_level TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_movement TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_accounts_code (code),
    KEY idx_accounts_parent (parent_id),
    KEY idx_accounts_nature (nature),
    CONSTRAINT fk_accounts_parent FOREIGN KEY (parent_id) REFERENCES accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE journals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(255) NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_journals_code (code)
) ENGINE=InnoDB;

CREATE TABLE entry_series (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fiscal_year_id INT UNSIGNED NOT NULL,
    code VARCHAR(20) NOT NULL,
    description VARCHAR(120) NULL,
    next_number INT UNSIGNED NOT NULL DEFAULT 1,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_series_year_code (fiscal_year_id, code),
    CONSTRAINT fk_series_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id)
) ENGINE=InnoDB;

CREATE TABLE accounting_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    client_id INT UNSIGNED NULL,
    client_period_id INT UNSIGNED NULL,
    fiscal_year_id INT UNSIGNED NOT NULL,
    journal_id INT UNSIGNED NOT NULL,
    series_id INT UNSIGNED NOT NULL,
    entry_number VARCHAR(40) NOT NULL,
    document_date DATE NOT NULL,
    posting_date DATE NOT NULL,
    document_reference VARCHAR(80) NULL,
    description VARCHAR(255) NOT NULL,
    status ENUM('draft','posted','void') NOT NULL DEFAULT 'posted',
    void_reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_entry_number (fiscal_year_id, series_id, entry_number),
    KEY idx_entry_dates (posting_date, document_date),
    KEY idx_entry_journal (journal_id),
    CONSTRAINT fk_entry_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_entry_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    CONSTRAINT fk_entry_client_period FOREIGN KEY (client_period_id) REFERENCES client_periods(id) ON DELETE SET NULL,
    CONSTRAINT fk_entry_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
    CONSTRAINT fk_entry_journal FOREIGN KEY (journal_id) REFERENCES journals(id),
    CONSTRAINT fk_entry_series FOREIGN KEY (series_id) REFERENCES entry_series(id)
) ENGINE=InnoDB;

CREATE TABLE accounting_entry_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    line_description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_line_account (account_id),
    KEY idx_line_entry (entry_id),
    CONSTRAINT fk_line_entry FOREIGN KEY (entry_id) REFERENCES accounting_entries(id) ON DELETE CASCADE,
    CONSTRAINT fk_line_account FOREIGN KEY (account_id) REFERENCES accounts(id),
    CONSTRAINT chk_line_single_side CHECK (
        (debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0)
    )
) ENGINE=InnoDB;

CREATE TABLE clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    tax_number VARCHAR(60) NULL,
    email VARCHAR(120) NULL,
    phone VARCHAR(60) NULL,
    address VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_clients_name (name),
    KEY idx_clients_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE client_periods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    fiscal_year_id INT UNSIGNED NOT NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NOT NULL,
    starts_on DATE NOT NULL,
    ends_on DATE NOT NULL,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_client_period_month (client_id, period_year, period_month),
    KEY idx_client_period_status (status),
    CONSTRAINT fk_client_period_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_period_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
    CONSTRAINT chk_client_period_month CHECK (period_month BETWEEN 1 AND 12),
    CONSTRAINT chk_client_period_dates CHECK (starts_on <= ends_on)
) ENGINE=InnoDB;

CREATE TABLE client_monthly_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    client_period_id INT UNSIGNED NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NOT NULL,
    token CHAR(64) NOT NULL,
    expires_on DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_client_period (client_id, period_year, period_month),
    UNIQUE KEY uk_client_link_period (client_period_id),
    UNIQUE KEY uk_client_link_token (token),
    KEY idx_client_link_period (period_year, period_month),
    CONSTRAINT fk_client_link_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_link_period FOREIGN KEY (client_period_id) REFERENCES client_periods(id) ON DELETE CASCADE,
    CONSTRAINT chk_client_link_month CHECK (period_month BETWEEN 1 AND 12)
) ENGINE=InnoDB;

CREATE TABLE client_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_link_id INT UNSIGNED NOT NULL,
    accounting_entry_id INT UNSIGNED NULL,
    transaction_type ENUM('income','expense') NOT NULL,
    transaction_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    description VARCHAR(255) NOT NULL,
    category VARCHAR(120) NULL,
    document_reference VARCHAR(80) NULL,
    document_path VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    review_notes VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL,
    KEY idx_client_transactions_status (status),
    KEY idx_client_transactions_date (transaction_date),
    CONSTRAINT fk_client_transaction_link FOREIGN KEY (client_link_id) REFERENCES client_monthly_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_transaction_entry FOREIGN KEY (accounting_entry_id) REFERENCES accounting_entries(id) ON DELETE SET NULL,
    CONSTRAINT chk_client_transaction_amount CHECK (amount > 0)
) ENGINE=InnoDB;

INSERT INTO companies (name, base_currency)
VALUES ('Empresa Demo', 'MZN');

INSERT INTO users (name, email, password_hash, role, is_active)
VALUES ('Administrador', 'admin@contabilidade.local', '$2y$10$3pAhWYifPhHl1Dik.7d2V.m2h2zl99dV0wwMcp91XbvhiU3mBJHwe', 'admin', 1);

INSERT INTO fiscal_years (company_id, name, starts_on, ends_on, status)
VALUES (1, '2026', '2026-01-01', '2026-12-31', 'open');

INSERT INTO entry_series (fiscal_year_id, code, description, next_number, is_default)
VALUES (1, 'LC', 'Lancamentos contabilisticos', 1, 1);

INSERT INTO journals (code, name, description, is_system, is_active) VALUES
('CX', 'Caixa', 'Movimentos de caixa', 1, 1),
('BC', 'Banco', 'Movimentos bancarios', 1, 1),
('CP', 'Compras', 'Diario de compras', 1, 1),
('VD', 'Vendas', 'Diario de vendas', 1, 1),
('GR', 'Geral', 'Lancamentos gerais', 1, 1);

INSERT INTO accounts (parent_id, code, name, nature, account_level, is_movement, is_active) VALUES
(NULL, '1', 'Ativo', 'asset', 1, 0, 1),
(NULL, '2', 'Passivo', 'liability', 1, 0, 1),
(NULL, '3', 'Capital Proprio', 'equity', 1, 0, 1),
(NULL, '4', 'Rendimentos', 'income', 1, 0, 1),
(NULL, '5', 'Gastos', 'expense', 1, 0, 1),
(1, '11', 'Caixa e bancos', 'asset', 2, 0, 1),
(6, '111', 'Caixa', 'asset', 3, 1, 1),
(6, '112', 'Banco', 'asset', 3, 1, 1),
(2, '21', 'Fornecedores diversos', 'liability', 2, 1, 1),
(3, '31', 'Capital social', 'equity', 2, 1, 1),
(4, '41', 'Vendas e prestacao de servicos', 'income', 2, 1, 1),
(5, '51', 'Compras e servicos externos', 'expense', 2, 1, 1);