CREATE TABLE IF NOT EXISTS sale_returns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    return_number VARCHAR(40) NOT NULL,
    sale_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NULL,
    returned_by BIGINT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,
    total DECIMAL(14,2) NOT NULL,
    debt_reduction DECIMAL(14,2) NOT NULL DEFAULT 0,
    cash_refund DECIMAL(14,2) NOT NULL DEFAULT 0,
    refund_account ENUM('employee', 'company') NULL,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NULL,
    returned_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY sale_returns_number_unique (return_number),
    KEY sale_returns_sale_date_index (sale_id, returned_at),
    KEY sale_returns_employee_date_index (employee_id, returned_at),
    CONSTRAINT sale_returns_sale_fk FOREIGN KEY (sale_id) REFERENCES sales(id),
    CONSTRAINT sale_returns_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT sale_returns_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT sale_returns_returned_by_fk FOREIGN KEY (returned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sale_return_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_return_id BIGINT UNSIGNED NOT NULL,
    sale_line_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    line_total DECIMAL(14,2) NOT NULL,
    stock_disposition ENUM('saleable', 'review') NOT NULL,
    created_at DATETIME NOT NULL,
    KEY sale_return_lines_return_index (sale_return_id),
    KEY sale_return_lines_sale_line_index (sale_line_id),
    CONSTRAINT sale_return_lines_return_fk FOREIGN KEY (sale_return_id) REFERENCES sale_returns(id),
    CONSTRAINT sale_return_lines_sale_line_fk FOREIGN KEY (sale_line_id) REFERENCES sale_lines(id),
    CONSTRAINT sale_return_lines_product_fk FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;