-- Migration: 005_create_cash_and_financial_tables.sql
-- Cria as tabelas para os módulos de caixa e financeiro

-- Tabela de caixas
CREATE TABLE IF NOT EXISTS cash_registers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_is_active (is_active)
);

-- Tabela de formas de pagamento
CREATE TABLE IF NOT EXISTS payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('cash', 'credit_card', 'debit_card', 'pix', 'bank_transfer', 'check', 'other') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    requires_change BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_type (type),
    INDEX idx_is_active (is_active)
);

-- Tabela de contas bancárias
CREATE TABLE IF NOT EXISTS bank_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    bank_name VARCHAR(100),
    account_number VARCHAR(50),
    agency VARCHAR(20),
    account_type ENUM('checking', 'savings', 'investment') DEFAULT 'checking',
    balance DECIMAL(15,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_is_active (is_active)
);

-- Tabela de categorias financeiras
CREATE TABLE IF NOT EXISTS financial_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    parent_id INT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_type (type),
    INDEX idx_parent_id (parent_id),
    INDEX idx_is_active (is_active),
    FOREIGN KEY (parent_id) REFERENCES financial_categories(id) ON DELETE SET NULL
);

-- Tabela de aberturas de caixa
CREATE TABLE IF NOT EXISTS cash_openings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    cash_register_id INT NOT NULL,
    opened_by INT NOT NULL,
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    opening_notes TEXT,
    opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('open', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_cash_register_id (cash_register_id),
    INDEX idx_opened_by (opened_by),
    INDEX idx_status (status),
    INDEX idx_opened_at (opened_at),
    FOREIGN KEY (cash_register_id) REFERENCES cash_registers(id)
);

-- Tabela de fechamentos de caixa
CREATE TABLE IF NOT EXISTS cash_closings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    cash_register_id INT NOT NULL,
    cash_opening_id INT NOT NULL,
    closed_by INT NOT NULL,
    expected_balance DECIMAL(15,2) DEFAULT 0.00,
    actual_balance DECIMAL(15,2) DEFAULT 0.00,
    difference DECIMAL(15,2) DEFAULT 0.00,
    closing_notes TEXT,
    closed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'confirmed', 'adjusted') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_cash_register_id (cash_register_id),
    INDEX idx_cash_opening_id (cash_opening_id),
    INDEX idx_closed_by (closed_by),
    INDEX idx_status (status),
    INDEX idx_closed_at (closed_at),
    FOREIGN KEY (cash_register_id) REFERENCES cash_registers(id),
    FOREIGN KEY (cash_opening_id) REFERENCES cash_openings(id)
);

-- Tabela de comandas/pedidos
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    client_id INT NULL,
    professional_id INT NULL,
    cash_register_id INT NULL,
    cash_opening_id INT NULL,
    status ENUM('open', 'pending_payment', 'paid', 'cancelled', 'refunded') DEFAULT 'open',
    order_type ENUM('service', 'product', 'mixed') DEFAULT 'service',
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    final_amount DECIMAL(15,2) DEFAULT 0.00,
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_order_number (order_number),
    INDEX idx_client_id (client_id),
    INDEX idx_professional_id (professional_id),
    INDEX idx_cash_register_id (cash_register_id),
    INDEX idx_cash_opening_id (cash_opening_id),
    INDEX idx_status (status),
    INDEX idx_order_type (order_type),
    INDEX idx_created_at (created_at),
    UNIQUE KEY unique_order_number (company_id, order_number)
);

-- Tabela de itens da comanda
CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    order_id INT NOT NULL,
    service_id INT NULL,
    product_id INT NULL,
    item_type ENUM('service', 'product') NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) DEFAULT 1.00,
    unit_price DECIMAL(15,2) NOT NULL,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    total_amount DECIMAL(15,2) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_order_id (order_id),
    INDEX idx_service_id (service_id),
    INDEX idx_product_id (product_id),
    INDEX idx_item_type (item_type),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Tabela de pagamentos da comanda
CREATE TABLE IF NOT EXISTS order_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    order_id INT NOT NULL,
    payment_method_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    change_amount DECIMAL(15,2) DEFAULT 0.00,
    transaction_id VARCHAR(255) NULL,
    external_reference VARCHAR(255) NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'completed', 'failed', 'cancelled', 'refunded') DEFAULT 'pending',
    notes TEXT,
    processed_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_order_id (order_id),
    INDEX idx_payment_method_id (payment_method_id),
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_status (status),
    INDEX idx_payment_date (payment_date),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

-- Tabela de contas a receber
CREATE TABLE IF NOT EXISTS accounts_receivable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    client_id INT NULL,
    order_id INT NULL,
    membership_id INT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    payment_date DATE NULL,
    status ENUM('pending', 'overdue', 'paid', 'cancelled') DEFAULT 'pending',
    payment_method_id INT NULL,
    transaction_id VARCHAR(255) NULL,
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_client_id (client_id),
    INDEX idx_order_id (order_id),
    INDEX idx_membership_id (membership_id),
    INDEX idx_due_date (due_date),
    INDEX idx_payment_date (payment_date),
    INDEX idx_status (status),
    INDEX idx_payment_method_id (payment_method_id)
);

-- Tabela de contas a pagar
CREATE TABLE IF NOT EXISTS accounts_payable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    supplier_name VARCHAR(255) NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    payment_date DATE NULL,
    status ENUM('pending', 'overdue', 'paid', 'cancelled') DEFAULT 'pending',
    payment_method_id INT NULL,
    bank_account_id INT NULL,
    transaction_id VARCHAR(255) NULL,
    category_id INT NULL,
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_supplier_name (supplier_name),
    INDEX idx_due_date (due_date),
    INDEX idx_payment_date (payment_date),
    INDEX idx_status (status),
    INDEX idx_payment_method_id (payment_method_id),
    INDEX idx_bank_account_id (bank_account_id),
    INDEX idx_category_id (category_id),
    FOREIGN KEY (category_id) REFERENCES financial_categories(id)
);

-- Tabela de transações financeiras
CREATE TABLE IF NOT EXISTS financial_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    transaction_type ENUM('income', 'expense', 'transfer', 'adjustment') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    description VARCHAR(255) NOT NULL,
    category_id INT NULL,
    bank_account_id INT NULL,
    payment_method_id INT NULL,
    order_id INT NULL,
    order_payment_id INT NULL,
    accounts_receivable_id INT NULL,
    accounts_payable_id INT NULL,
    membership_payment_id INT NULL,
    cash_register_id INT NULL,
    cash_opening_id INT NULL,
    transaction_date DATE NOT NULL,
    transaction_time TIME DEFAULT CURRENT_TIME,
    reference_type VARCHAR(50) NULL,
    reference_id INT NULL,
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_category_id (category_id),
    INDEX idx_bank_account_id (bank_account_id),
    INDEX idx_payment_method_id (payment_method_id),
    INDEX idx_order_id (order_id),
    INDEX idx_order_payment_id (order_payment_id),
    INDEX idx_accounts_receivable_id (accounts_receivable_id),
    INDEX idx_accounts_payable_id (accounts_payable_id),
    INDEX idx_membership_payment_id (membership_payment_id),
    INDEX idx_cash_register_id (cash_register_id),
    INDEX idx_cash_opening_id (cash_opening_id),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_reference_type (reference_type),
    INDEX idx_reference_id (reference_id),
    FOREIGN KEY (category_id) REFERENCES financial_categories(id),
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id),
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (order_payment_id) REFERENCES order_payments(id),
    FOREIGN KEY (accounts_receivable_id) REFERENCES accounts_receivable(id),
    FOREIGN KEY (accounts_payable_id) REFERENCES accounts_payable(id),
    FOREIGN KEY (cash_register_id) REFERENCES cash_registers(id),
    FOREIGN KEY (cash_opening_id) REFERENCES cash_openings(id)
);

-- Tabela de auditoria para estornos e cancelamentos
CREATE TABLE IF NOT EXISTS financial_audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    action_type ENUM('cancel_order', 'refund_payment', 'void_transaction', 'adjust_balance', 'reverse_transaction') NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    original_amount DECIMAL(15,2) NULL,
    adjustment_amount DECIMAL(15,2) NULL,
    reason TEXT NOT NULL,
    reference_transaction_id INT NULL,
    audit_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_user_id (user_id),
    INDEX idx_action_type (action_type),
    INDEX idx_entity_type (entity_type),
    INDEX idx_entity_id (entity_id),
    INDEX idx_reference_transaction_id (reference_transaction_id)
);

-- Inserir dados padrão para formas de pagamento
INSERT INTO payment_methods (company_id, name, type, is_active, requires_change) VALUES
(1, 'Dinheiro', 'cash', 1, 1),
(1, 'Cartão de Crédito', 'credit_card', 1, 0),
(1, 'Cartão de Débito', 'debit_card', 1, 0),
(1, 'PIX', 'pix', 1, 0),
(1, 'Transferência Bancária', 'bank_transfer', 1, 0);

-- Inserir dados padrão para categorias financeiras
INSERT INTO financial_categories (company_id, name, type, is_active) VALUES
(1, 'Vendas de Serviços', 'income', 1),
(1, 'Vendas de Produtos', 'income', 1),
(1, 'Pagamentos de Assinatura', 'income', 1),
(1, 'Aluguel', 'expense', 1),
(1, 'Salários', 'expense', 1),
(1, 'Fornecedores', 'expense', 1),
(1, 'Marketing', 'expense', 1),
(1, 'Manutenção', 'expense', 1),
(1, 'Impostos', 'expense', 1),
(1, 'Outros Recebimentos', 'income', 1),
(1, 'Outros Gastos', 'expense', 1);