-- ============================================================
-- SCHEMA PENTRU SISTEMUL DE CATEGORII CU MEGA-MENU
-- ============================================================
-- Versiune: 1.0
-- Data: 2025-11-30
-- Descriere: Structura completa pentru gestionarea categoriilor 
--            de produse cu suport pentru mega-menu dinamic
-- ============================================================

-- Dezactivare verificari pentru import usor
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- ============================================================
-- Tabelul: categories
-- Descriere: Stocheaza ierarhia de categorii pe multiple niveluri
-- ============================================================
CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `parent_id` INT(11) DEFAULT NULL COMMENT 'ID-ul categoriei parinte (NULL pentru categorii root)',
    `name` VARCHAR(255) NOT NULL COMMENT 'Numele categoriei',
    `slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly identifier unic',
    `description` TEXT DEFAULT NULL COMMENT 'Descriere detaliata a categoriei',
    `icon` VARCHAR(100) DEFAULT NULL COMMENT 'Clasa CSS pentru iconita (ex: ci-computer)',
    `image_url` VARCHAR(500) DEFAULT NULL COMMENT 'URL imagine reprezentativa',
    `url` VARCHAR(500) DEFAULT NULL COMMENT 'Link catre pagina de categorie',
    `position` INT(11) DEFAULT 0 COMMENT 'Ordinea de afisare (mai mic = mai sus)',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT 'Categorie activa (1) sau dezactivata (0)',
    `show_in_menu` TINYINT(1) DEFAULT 1 COMMENT 'Arata in mega-menu (1=da, 0=nu)',
    `has_megamenu` TINYINT(1) DEFAULT 0 COMMENT 'Are submeniu extins - mega-menu (1=da, 0=nu)',
    `megamenu_layout` VARCHAR(50) DEFAULT 'auto' COMMENT 'Layout mega-menu: auto, 2-col, 3-col, 4-col',
    `seo_title` VARCHAR(255) DEFAULT NULL COMMENT 'Titlu SEO pentru pagina categoriei',
    `seo_description` TEXT DEFAULT NULL COMMENT 'Meta description pentru SEO',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    KEY `idx_parent` (`parent_id`),
    KEY `idx_position` (`position`),
    KEY `idx_active` (`is_active`),
    KEY `idx_show_in_menu` (`show_in_menu`),
    CONSTRAINT `fk_categories_parent` 
        FOREIGN KEY (`parent_id`) 
        REFERENCES `categories` (`id`) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Categorii de produse cu ierarhie';

-- ============================================================
-- Tabelul: category_promo_cards
-- Descriere: Carduri promotionale afisate in mega-menu pentru fiecare categorie
-- ============================================================
CREATE TABLE IF NOT EXISTS `category_promo_cards` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `category_id` INT(11) NOT NULL COMMENT 'ID categoria parinte pentru care apare cardul',
    `title` VARCHAR(255) NOT NULL COMMENT 'Titlul produsului/ofertei',
    `subtitle` TEXT DEFAULT NULL COMMENT 'Subtitlu (ex: Deal of the week, Best deal)',
    `badge_text` VARCHAR(100) DEFAULT NULL COMMENT 'Text badge (ex: Save up to $400)',
    `badge_bg_color` VARCHAR(50) DEFAULT 'danger' COMMENT 'Culoare badge: danger, success, warning, info, etc',
    `description` TEXT DEFAULT NULL COMMENT 'Descriere suplimentara',
    `price_original` DECIMAL(10,2) DEFAULT NULL COMMENT 'Pret original (taiat)',
    `price_sale` DECIMAL(10,2) DEFAULT NULL COMMENT 'Pret promotional',
    `price_label` VARCHAR(100) DEFAULT NULL COMMENT 'Label pret (ex: Starts from)',
    `image_url` VARCHAR(500) NOT NULL COMMENT 'URL imagine produs',
    `image_alt` VARCHAR(255) DEFAULT NULL COMMENT 'Text alternativ pentru imagine (SEO)',
    `icon` VARCHAR(100) DEFAULT NULL COMMENT 'Icon brand optional (ex: ci-apple, ci-google)',
    `button_text` VARCHAR(100) DEFAULT 'Shop now' COMMENT 'Text buton CTA',
    `button_url` VARCHAR(500) NOT NULL COMMENT 'Link buton CTA',
    `bg_color` VARCHAR(50) DEFAULT 'bg-body-secondary' COMMENT 'Clasa CSS pentru culoare fundal',
    `position` INT(11) DEFAULT 0 COMMENT 'Ordinea de afisare',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT 'Card activ (1) sau inactiv (0)',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_category` (`category_id`),
    KEY `idx_active` (`is_active`),
    KEY `idx_position` (`position`),
    CONSTRAINT `fk_promo_cards_category` 
        FOREIGN KEY (`category_id`) 
        REFERENCES `categories` (`id`) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Carduri promotionale pentru mega-menu';

-- ============================================================
-- Tabelul: category_settings
-- Descriere: Setari globale pentru comportamentul mega-menu
-- ============================================================
CREATE TABLE IF NOT EXISTS `category_settings` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL COMMENT 'Cheie unica pentru setare',
    `setting_value` TEXT DEFAULT NULL COMMENT 'Valoarea setarii',
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'Descriere setare',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Setari globale pentru mega-menu';

-- ============================================================
-- Date initiale pentru category_settings
-- ============================================================
INSERT INTO `category_settings` (`setting_key`, `setting_value`, `description`) VALUES
('homepage_static_menu', '1', 'Mega-menu static pe homepage (1=da, 0=nu)'),
('hover_trigger', 'hover', 'Trigger pentru deschidere: hover sau click'),
('hover_delay', '200', 'Delay hover in milisecunde'),
('max_megamenu_columns', '3', 'Numar maxim de coloane in mega-menu (fara promo card)'),
('show_mobile_all_categories', '1', 'Arata butonul All Categories pe mobile (1=da, 0=nu)');

-- Reactivare verificari
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

-- ============================================================
-- Comentarii finale
-- ============================================================
-- Pentru a importa acest fisier in MySQL:
-- mysql -u username -p database_name < 01_schema.sql
--
-- SAU prin phpMyAdmin -> Import -> Selectati fisierul
--
-- Pentru a reseta complet tabelele:
-- DROP TABLE IF EXISTS category_promo_cards;
-- DROP TABLE IF EXISTS category_settings;
-- DROP TABLE IF EXISTS categories;
-- apoi rulati din nou 01_schema.sql
-- ============================================================


