USE heladospinguino_db;

SET @db_name = DATABASE();

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'legal_name'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN legal_name VARCHAR(200) NULL AFTER name',
  'SELECT "legal_name exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'tax_id'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN tax_id VARCHAR(80) NULL AFTER legal_name',
  'SELECT "tax_id exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'fiscal_address'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN fiscal_address VARCHAR(255) NULL AFTER tax_id',
  'SELECT "fiscal_address exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'contact_email'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN contact_email VARCHAR(190) NULL AFTER fiscal_address',
  'SELECT "contact_email exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'contact_phone'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN contact_phone VARCHAR(60) NULL AFTER contact_email',
  'SELECT "contact_phone exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tenants' AND COLUMN_NAME = 'logo_url'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE tenants ADD COLUMN logo_url VARCHAR(255) NULL AFTER contact_phone',
  'SELECT "logo_url exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS menus (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NULL,
    parent_id BIGINT UNSIGNED NULL,
    title VARCHAR(120) NOT NULL,
    url VARCHAR(190) NOT NULL,
    icon VARCHAR(80) NOT NULL DEFAULT 'bx bx-circle',
    sort_order INT NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_menus_tenant_active (tenant_id, is_active),
    KEY idx_menus_parent (parent_id)
) ENGINE=InnoDB;

SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = @db_name
    AND TABLE_NAME = 'menus'
    AND CONSTRAINT_NAME = 'fk_menus_tenant'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE menus ADD CONSTRAINT fk_menus_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)',
  'SELECT "fk_menus_tenant exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = @db_name
    AND TABLE_NAME = 'menus'
    AND CONSTRAINT_NAME = 'fk_menus_parent'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE menus ADD CONSTRAINT fk_menus_parent FOREIGN KEY (parent_id) REFERENCES menus(id)',
  'SELECT "fk_menus_parent exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS menu_role_access (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'admin', 'sales', 'viewer') NOT NULL,
    UNIQUE KEY uq_menu_role (menu_id, role)
) ENGINE=InnoDB;

SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = @db_name
    AND TABLE_NAME = 'menu_role_access'
    AND CONSTRAINT_NAME = 'fk_menu_role_menu'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE menu_role_access ADD CONSTRAINT fk_menu_role_menu FOREIGN KEY (menu_id) REFERENCES menus(id)',
  'SELECT "fk_menu_role_menu exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Dashboard', 'hp-dashboard.html', 'bx bx-home-circle', 10, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Dashboard' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Productos', 'hp-products.html', 'bx bx-package', 20, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Productos' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Remisiones', 'hp-remisions.html', 'bx bx-receipt', 30, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Remisiones' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Empresa', 'hp-company.html', 'bx bx-building-house', 40, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Empresa' LIMIT 1);

INSERT INTO menu_role_access (menu_id, role)
SELECT m.id, roles.role_name
FROM menus m
JOIN (
    SELECT 'owner' AS role_name
    UNION ALL SELECT 'admin'
    UNION ALL SELECT 'sales'
    UNION ALL SELECT 'viewer'
) roles
WHERE m.tenant_id IS NULL
  AND m.title IN ('Dashboard', 'Productos', 'Remisiones', 'Empresa')
  AND NOT EXISTS (
      SELECT 1 FROM menu_role_access x
      WHERE x.menu_id = m.id AND x.role = roles.role_name
  );