USE contabilidade_core;

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS client_monthly_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT 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_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 chk_client_link_month CHECK (period_month BETWEEN 1 AND 12)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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;