CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    document VARCHAR(40) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(40) NULL,
    address VARCHAR(255) NULL,
    asaas_customer_id VARCHAR(100) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX (asaas_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS financial_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type ENUM('income','expense') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incomes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NULL,
    category_id INT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    due_date DATE NOT NULL,
    received_date DATE NULL,
    type ENUM('recurring','one_time','installment') DEFAULT 'one_time',
    status ENUM('pending','received','overdue','cancelled') DEFAULT 'pending',
    payment_method VARCHAR(80) NULL,
    asaas_payment_id VARCHAR(100) NULL,
    installment_number INT NULL,
    total_installments INT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX (asaas_payment_id),
    INDEX (due_date),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES financial_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    supplier VARCHAR(255) NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    due_date DATE NOT NULL,
    paid_date DATE NULL,
    type ENUM('fixed','variable','recurring','installment') DEFAULT 'variable',
    status ENUM('pending','paid','overdue','cancelled') DEFAULT 'pending',
    payment_method VARCHAR(80) NULL,
    installment_number INT NULL,
    total_installments INT NULL,
    receipt_file VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX (due_date),
    FOREIGN KEY (category_id) REFERENCES financial_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monthly_goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    month INT NOT NULL,
    year INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    UNIQUE KEY unique_goal (month, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    service_name VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NULL,
    start_date DATE NULL,
    renewal_date DATE NULL,
    status ENUM('active','expired','cancelled') DEFAULT 'active',
    contract_file VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    income_id INT NULL,
    person_name VARCHAR(255) NOT NULL,
    calculation_type ENUM('fixed','percentage') DEFAULT 'fixed',
    percentage DECIMAL(5,2) NULL,
    amount DECIMAL(12,2) NOT NULL,
    due_date DATE NULL,
    paid_date DATE NULL,
    status ENUM('pending','paid','cancelled') DEFAULT 'pending',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (income_id) REFERENCES incomes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS taxes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    rate DECIMAL(5,2) NULL,
    amount DECIMAL(12,2) NOT NULL,
    due_date DATE NULL,
    paid_date DATE NULL,
    status ENUM('pending','paid','overdue','cancelled') DEFAULT 'pending',
    receipt_file VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asaas_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(120) NULL,
    event_name VARCHAR(120) NULL,
    payment_id VARCHAR(120) NULL,
    payload LONGTEXT NULL,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_event (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;