CREATE TABLE IF NOT EXISTS loan_sub_products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_type VARCHAR(100) NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX idx_loan_sub_products_slug ON loan_sub_products (slug);
CREATE INDEX idx_loan_sub_products_product ON loan_sub_products (product_type, is_active);

ALTER TABLE loan_proposals
    ADD COLUMN sub_product_id INT UNSIGNED NULL AFTER product_type,
    ADD CONSTRAINT fk_loan_proposals_sub_product FOREIGN KEY (sub_product_id)
        REFERENCES loan_sub_products (id) ON DELETE SET NULL;

ALTER TABLE sales_goals
    ADD COLUMN sub_product_id INT UNSIGNED NULL AFTER user_id,
    ADD CONSTRAINT fk_sales_goals_sub_product FOREIGN KEY (sub_product_id)
        REFERENCES loan_sub_products (id) ON DELETE CASCADE;

DROP INDEX idx_sales_goals_unique_period_user ON sales_goals;
CREATE UNIQUE INDEX idx_sales_goals_period_user_sub ON sales_goals (goal_month, user_id, sub_product_id);

INSERT INTO loan_sub_products (product_type, name, slug, display_order) VALUES
    (NULL, 'Portabilidade', 'portabilidade', 1),
    (NULL, 'Refin de Portabilidade', 'refin_portabilidade', 2),
    (NULL, 'Margem Livre', 'margem_livre', 3),
    (NULL, 'Refinanciamento', 'refinanciamento', 4),
    (NULL, 'Saque Complementar', 'saque_complementar', 5),
    (NULL, 'Emprestimo Pessoal', 'emprestimo_pessoal', 6),
    (NULL, 'Margem de Aumento', 'margem_aumento', 7);