CREATE TABLE product_plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    label VARCHAR(120) NOT NULL,
    description VARCHAR(255) NULL,
    max_applications SMALLINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE plan_features (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_code VARCHAR(50) NOT NULL,
    feature_key VARCHAR(120) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_plan_feature (plan_code, feature_key),
    CONSTRAINT fk_plan_features_plan FOREIGN KEY (plan_code) REFERENCES product_plans (code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE company_feature_overrides (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    feature_key VARCHAR(120) NOT NULL,
    is_allowed TINYINT(1) NOT NULL DEFAULT 1,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_feature (company_id, feature_key),
    CONSTRAINT fk_company_feature_company FOREIGN KEY (company_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE users
    ADD COLUMN company_size ENUM('pequena', 'media', 'grande') NULL AFTER broker_id,
    ADD COLUMN plan_code VARCHAR(50) NULL AFTER company_size;

ALTER TABLE users
    ADD CONSTRAINT fk_users_plan_code FOREIGN KEY (plan_code) REFERENCES product_plans (code) ON DELETE SET NULL;

INSERT INTO product_plans (code, label, description, max_applications)
VALUES
    ('basic', 'Plano Básico', 'Funcionalidades essenciais para pequenas equipes.', 3),
    ('intermediate', 'Plano Intermediário', 'Inclui módulos financeiros para acompanhar resultados.', 6),
    ('complete', 'Plano Completo', 'Todas as aplicações e integrações avançadas.', 12);

INSERT INTO plan_features (plan_code, feature_key)
VALUES
    ('basic', 'dashboard.access'),
    ('basic', 'leads.core'),
    ('intermediate', 'dashboard.access'),
    ('intermediate', 'leads.core'),
    ('intermediate', 'finance.center'),
    ('complete', 'dashboard.access'),
    ('complete', 'leads.core'),
    ('complete', 'finance.center'),
    ('complete', 'lead.analieasy'),
    ('complete', 'automation.tasks');

UPDATE users
SET
    company_size = COALESCE(company_size, 'grande'),
    plan_code = 'complete'
WHERE role = 'broker';

UPDATE product_plans
SET label = 'Plano Básico', description = 'Funcionalidades essenciais para pequenas equipes.'
WHERE code = 'basic';

UPDATE product_plans
SET label = 'Plano Intermediário', description = 'Inclui módulos financeiros para acompanhar resultados.'
WHERE code = 'intermediate';

UPDATE product_plans
SET label = 'Plano Completo', description = 'Todas as aplicações e integrações avançadas.'
WHERE code = 'complete';