-- ════════════════════════════════════════════════════════════════════════════
--  INSTALARE CURATĂ — platformă hub + sateliți (Cars · RV · Heavy)
--  adminkartisistem.com
--  ──────────────────────────────────────────────────────────────────────────
--  Rulează o singură dată, în phpMyAdmin, pe baza NOUĂ și GOALĂ.
--  Conține: structura completă, categoriile de inventar și un cont de admin.
--  NU conține date vechi (fără anunțuri, fără comenzi, fără mesaje).
-- ════════════════════════════════════════════════════════════════════════════

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- ════════════════════════════════════════════════════════════════════════════
--  PARTEA 1 — tabelele de bază
-- ════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `jurisdiction` varchar(2) NOT NULL DEFAULT 'uk',
  `company_name` varchar(255) NOT NULL,
  `company_short_name` varchar(100) DEFAULT NULL,
  `company_number` varchar(100) DEFAULT NULL,
  `year_founded` smallint(6) DEFAULT NULL,
  `theme_color` varchar(7) DEFAULT '#0d6efd',
  `logo_path` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(50) DEFAULT NULL,
  `address_line1` varchar(255) DEFAULT NULL,
  `address_line2` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(64) DEFAULT NULL,
  `postcode` varchar(20) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'United Kingdom',
  `bank_name` varchar(255) DEFAULT NULL,
  `bank_account_name` varchar(255) DEFAULT NULL,
  `bank_sort_code` varchar(20) DEFAULT NULL,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  `role` enum('super_admin','site_admin') NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `make` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `variant` varchar(100) DEFAULT NULL,
  `registration_number` varchar(20) DEFAULT NULL,
  `year` smallint(6) DEFAULT NULL,
  `mileage` int(11) DEFAULT NULL,
  `fuel` varchar(50) DEFAULT NULL,
  `transmission` varchar(50) DEFAULT NULL,
  `body_type` varchar(50) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `mot_expiry` date DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('available','reserved','sold') NOT NULL DEFAULT 'available',
  `description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `vehicle_images` (
  `id` int(11) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `path` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `vehicle_sites` (
  `vehicle_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `reviews` (
  `id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  `author_name` varchar(255) NOT NULL,
  `rating` tinyint(4) NOT NULL DEFAULT 5,
  `content` text NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `is_approved` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `review_sites` (
  `review_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `vehicle_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ── chei primare și indecși ─────────────────────────────────────────────

ALTER TABLE `sites`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `domain` (`domain`);

ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE;

ALTER TABLE `vehicles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_slug` (`slug`);

ALTER TABLE `vehicle_images`
  ADD CONSTRAINT `vehicle_images_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE;

ALTER TABLE `vehicle_sites`
  ADD CONSTRAINT `fk_vs_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_vs_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE;

ALTER TABLE `reviews`
  ADD PRIMARY KEY (`id`),
  ADD KEY `site_id` (`site_id`);

ALTER TABLE `review_sites`
  ADD CONSTRAINT `fk_rs_review` FOREIGN KEY (`review_id`) REFERENCES `reviews` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_rs_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE;

ALTER TABLE `contact_messages`
  ADD CONSTRAINT `contact_messages_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `contact_messages_ibfk_2` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL;

-- ── numerotare automată ─────────────────────────────────────────────────

ALTER TABLE `sites`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `vehicles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `vehicle_images`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `contact_messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- ════════════════════════════════════════════════════════════════════════════
--  PARTEA 2 — extinderea pentru platforma multi-site
-- ════════════════════════════════════════════════════════════════════════════

-- ════════════════════════════════════════════════════════════════════════════
--  1. SITES — tot ce se setează din admin pentru fiecare satelit
-- ════════════════════════════════════════════════════════════════════════════

ALTER TABLE `sites`
  /* ── tipul site-ului și designul folosit ─────────────────────────── */
  ADD COLUMN IF NOT EXISTS `site_type`    ENUM('cars','rv','heavy') NOT NULL DEFAULT 'cars' AFTER `domain`,
  ADD COLUMN IF NOT EXISTS `theme`        VARCHAR(32)  NOT NULL DEFAULT 'cars'   AFTER `site_type`,

  /* ── identitate ──────────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `tagline`          VARCHAR(255) DEFAULT NULL AFTER `company_short_name`,
  ADD COLUMN IF NOT EXISTS `meta_description` VARCHAR(320) DEFAULT NULL AFTER `tagline`,
  ADD COLUMN IF NOT EXISTS `about_text`       TEXT         DEFAULT NULL AFTER `meta_description`,
  ADD COLUMN IF NOT EXISTS `vat_number`       VARCHAR(60)  DEFAULT NULL AFTER `company_number`,

  /* ── culori (theme_color rămâne culoarea principală) ─────────────── */
  ADD COLUMN IF NOT EXISTS `color_dark`   VARCHAR(7) DEFAULT NULL AFTER `theme_color`,
  ADD COLUMN IF NOT EXISTS `color_accent` VARCHAR(7) DEFAULT NULL AFTER `color_dark`,

  /* ── imagini (nume de fișier în uploads/sites/) ──────────────────── */
  ADD COLUMN IF NOT EXISTS `hero_image`   VARCHAR(255) DEFAULT NULL AFTER `logo_path`,
  ADD COLUMN IF NOT EXISTS `about_image`  VARCHAR(255) DEFAULT NULL AFTER `hero_image`,
  ADD COLUMN IF NOT EXISTS `og_image`     VARCHAR(255) DEFAULT NULL AFTER `about_image`,
  ADD COLUMN IF NOT EXISTS `favicon_path` VARCHAR(255) DEFAULT NULL AFTER `og_image`,

  /* ── program de lucru și timp de răspuns ─────────────────────────── */
  ADD COLUMN IF NOT EXISTS `hours_weekday`  VARCHAR(80) DEFAULT 'Mon–Fri 9:00 AM – 6:00 PM' AFTER `country`,
  ADD COLUMN IF NOT EXISTS `hours_saturday` VARCHAR(80) DEFAULT 'Sat 10:00 AM – 4:00 PM'    AFTER `hours_weekday`,
  ADD COLUMN IF NOT EXISTS `hours_sunday`   VARCHAR(80) DEFAULT 'Closed'                    AFTER `hours_saturday`,
  ADD COLUMN IF NOT EXISTS `reply_time`     VARCHAR(40) DEFAULT '48 hours'                  AFTER `hours_sunday`,

  /* ── rețele sociale ──────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `facebook`  VARCHAR(255) DEFAULT NULL AFTER `reply_time`,
  ADD COLUMN IF NOT EXISTS `instagram` VARCHAR(255) DEFAULT NULL AFTER `facebook`,
  ADD COLUMN IF NOT EXISTS `youtube`   VARCHAR(255) DEFAULT NULL AFTER `instagram`,
  ADD COLUMN IF NOT EXISTS `tiktok`    VARCHAR(255) DEFAULT NULL AFTER `youtube`,

  /* ── parametri comerciali afișați pe site ────────────────────────── */
  ADD COLUMN IF NOT EXISTS `inspection_days` TINYINT       NOT NULL DEFAULT 10    AFTER `tiktok`,
  ADD COLUMN IF NOT EXISTS `refund_hours`    SMALLINT      NOT NULL DEFAULT 24    AFTER `inspection_days`,
  ADD COLUMN IF NOT EXISTS `delivery_rate`   DECIMAL(6,2)  NOT NULL DEFAULT 0.60  AFTER `refund_hours`,
  ADD COLUMN IF NOT EXISTS `delivery_origin` VARCHAR(120)  DEFAULT NULL           AFTER `delivery_rate`,
  ADD COLUMN IF NOT EXISTS `currency_code`   VARCHAR(8)    NOT NULL DEFAULT 'USD' AFTER `delivery_origin`,
  ADD COLUMN IF NOT EXISTS `currency_symbol` VARCHAR(4)    NOT NULL DEFAULT '$'   AFTER `currency_code`,

  /* ── completare date bancare (pentru contract și factură) ────────── */
  ADD COLUMN IF NOT EXISTS `bank_swift`   VARCHAR(40) DEFAULT NULL AFTER `bank_account_number`,
  ADD COLUMN IF NOT EXISTS `bank_iban`    VARCHAR(64) DEFAULT NULL AFTER `bank_swift`,
  ADD COLUMN IF NOT EXISTS `bank_routing` VARCHAR(40) DEFAULT NULL AFTER `bank_iban`,

  /* ── analytics ───────────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `ga_id`  VARCHAR(40) DEFAULT NULL AFTER `bank_routing`,
  ADD COLUMN IF NOT EXISTS `gtm_id` VARCHAR(40) DEFAULT NULL AFTER `ga_id`;

-- site-ul existent rămâne de tip „cars" cu tema „cars" (valorile implicite)

-- ════════════════════════════════════════════════════════════════════════════
--  2. VEHICLES — câmpuri comune + specifice RV și Heavy
--     (coloane fixe, conform deciziei; cele nefolosite rămân goale)
-- ════════════════════════════════════════════════════════════════════════════

ALTER TABLE `vehicles`
  /* ── comercial / catalogare ──────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `stock_number`      VARCHAR(60)   DEFAULT NULL AFTER `registration_number`,
  ADD COLUMN IF NOT EXISTS `vin`               VARCHAR(64)   DEFAULT NULL AFTER `stock_number`,
  ADD COLUMN IF NOT EXISTS `category`          VARCHAR(80)   DEFAULT NULL AFTER `vin`,
  ADD COLUMN IF NOT EXISTS `subcategory`       VARCHAR(80)   DEFAULT NULL AFTER `category`,
  ADD COLUMN IF NOT EXISTS `short_description` VARCHAR(500)  DEFAULT NULL AFTER `subcategory`,
  ADD COLUMN IF NOT EXISTS `vehicle_condition` VARCHAR(60)   DEFAULT 'Used' AFTER `color`,
  ADD COLUMN IF NOT EXISTS `warranty`          VARCHAR(120)  DEFAULT NULL AFTER `vehicle_condition`,
  ADD COLUMN IF NOT EXISTS `engine_size`       VARCHAR(60)   DEFAULT NULL AFTER `fuel`,
  ADD COLUMN IF NOT EXISTS `drive_type`        VARCHAR(60)   DEFAULT NULL AFTER `transmission`,
  ADD COLUMN IF NOT EXISTS `video_url`         TEXT          DEFAULT NULL AFTER `description`,

  /* ── preț ────────────────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `price_shipping` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `price`,
  ADD COLUMN IF NOT EXISTS `price_total`    DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `price_shipping`,
  ADD COLUMN IF NOT EXISTS `currency_code`  VARCHAR(8)    NOT NULL DEFAULT 'USD' AFTER `price_total`,

  /* ── afișare ─────────────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`,
  ADD COLUMN IF NOT EXISTS `sort_order`  INT(11)    NOT NULL DEFAULT 0 AFTER `is_featured`,
  ADD COLUMN IF NOT EXISTS `views`       INT(11)    NOT NULL DEFAULT 0 AFTER `sort_order`,

  /* ── specific RV ─────────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `sleeps`      VARCHAR(20) DEFAULT NULL AFTER `views`,
  ADD COLUMN IF NOT EXISTS `length_ft`   VARCHAR(20) DEFAULT NULL AFTER `sleeps`,
  ADD COLUMN IF NOT EXISTS `slide_outs`  VARCHAR(20) DEFAULT NULL AFTER `length_ft`,
  ADD COLUMN IF NOT EXISTS `axles`       VARCHAR(20) DEFAULT NULL AFTER `slide_outs`,
  ADD COLUMN IF NOT EXISTS `gvwr`        VARCHAR(40) DEFAULT NULL AFTER `axles`,
  ADD COLUMN IF NOT EXISTS `fresh_water` VARCHAR(40) DEFAULT NULL AFTER `gvwr`,

  /* ── specific Heavy ──────────────────────────────────────────────── */
  ADD COLUMN IF NOT EXISTS `engine_hours`     INT(11)      DEFAULT NULL AFTER `fresh_water`,
  ADD COLUMN IF NOT EXISTS `serial_number`    VARCHAR(80)  DEFAULT NULL AFTER `engine_hours`,
  ADD COLUMN IF NOT EXISTS `operating_weight` VARCHAR(40)  DEFAULT NULL AFTER `serial_number`,
  ADD COLUMN IF NOT EXISTS `lift_capacity`    VARCHAR(40)  DEFAULT NULL AFTER `operating_weight`,
  ADD COLUMN IF NOT EXISTS `hydraulics`       VARCHAR(80)  DEFAULT NULL AFTER `lift_capacity`,
  ADD COLUMN IF NOT EXISTS `attachments`      VARCHAR(255) DEFAULT NULL AFTER `hydraulics`;

-- prețul total pentru anunțurile deja existente = prețul lor actual
UPDATE `vehicles` SET `price_total` = `price` WHERE `price_total` = 0 AND `price` > 0;

-- indecși utili (IF NOT EXISTS e suportat pe MariaDB 10.5+;
--  dacă dă eroare „duplicate key name", înseamnă că indexul există deja — ignoră)
ALTER TABLE `vehicles` ADD INDEX IF NOT EXISTS `idx_featured`  (`is_featured`);
ALTER TABLE `vehicles` ADD INDEX IF NOT EXISTS `idx_category`  (`category`,`subcategory`);
ALTER TABLE `vehicles` ADD INDEX IF NOT EXISTS `idx_slug`      (`slug`);

-- ════════════════════════════════════════════════════════════════════════════
--  3. CONTACT_MESSAGES — un singur tabel pentru toate formularele satelitului
-- ════════════════════════════════════════════════════════════════════════════

ALTER TABLE `contact_messages`
  ADD COLUMN IF NOT EXISTS `kind`       VARCHAR(30)  NOT NULL DEFAULT 'enquiry' AFTER `site_id`,
  ADD COLUMN IF NOT EXISTS `subject`    VARCHAR(255) DEFAULT NULL AFTER `phone`,
  ADD COLUMN IF NOT EXISTS `company`    VARCHAR(255) DEFAULT NULL AFTER `subject`,
  ADD COLUMN IF NOT EXISTS `address`    VARCHAR(500) DEFAULT NULL AFTER `company`,
  ADD COLUMN IF NOT EXISTS `extra`      TEXT         DEFAULT NULL AFTER `message`,
  ADD COLUMN IF NOT EXISTS `replied_at` DATETIME     DEFAULT NULL AFTER `is_read`;

-- kind: enquiry | order | valuation | financing | review | trade-in

-- ════════════════════════════════════════════════════════════════════════════
--  4. ORDERS — cererile de cumpărare venite din butonul „Buy It Now"
-- ════════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `orders` (
  `id`                    INT(11)       NOT NULL AUTO_INCREMENT,
  `site_id`               INT(11)       NOT NULL,
  `vehicle_id`            INT(11)       DEFAULT NULL,
  `order_uuid`            VARCHAR(40)   NOT NULL,
  `contract_id`           VARCHAR(50)   DEFAULT NULL,

  `buyer_name`            VARCHAR(255)  DEFAULT NULL,
  `buyer_company`         VARCHAR(255)  DEFAULT NULL,
  `buyer_email`           VARCHAR(255)  DEFAULT NULL,
  `buyer_phone`           VARCHAR(100)  DEFAULT NULL,
  `buyer_address`         VARCHAR(500)  DEFAULT NULL,
  `delivery_address`      VARCHAR(500)  DEFAULT NULL,

  `title_snapshot`        VARCHAR(255)  DEFAULT NULL,
  `price_snapshot`        DECIMAL(12,2) DEFAULT NULL,
  `shipping_snapshot`     DECIMAL(12,2) DEFAULT NULL,
  `total_snapshot`        DECIMAL(12,2) DEFAULT NULL,
  `currency_code`         VARCHAR(8)    DEFAULT 'USD',
  `spec_snapshot`         TEXT          DEFAULT NULL,
  `image_snapshot`        VARCHAR(255)  DEFAULT NULL,

  `payment_method`        VARCHAR(60)   DEFAULT 'Bank Wire Transfer',
  `payment_reference`     VARCHAR(100)  DEFAULT NULL,
  `order_status`          VARCHAR(50)   NOT NULL DEFAULT 'Registered',
  `email_status`          VARCHAR(30)   NOT NULL DEFAULT 'pending',

  `tracking_id`           VARCHAR(80)   DEFAULT NULL,
  `tracking_status`       VARCHAR(80)   DEFAULT NULL,
  `tracking_origin`       VARCHAR(255)  DEFAULT NULL,
  `tracking_destination`  VARCHAR(255)  DEFAULT NULL,
  `tracking_eta`          VARCHAR(120)  DEFAULT NULL,
  `tracking_updated_at`   DATETIME      DEFAULT NULL,

  `admin_notes`           TEXT          DEFAULT NULL,
  `buyer_ip`              VARCHAR(64)   DEFAULT NULL,
  `buyer_user_agent`      VARCHAR(500)  DEFAULT NULL,
  `created_at`            TIMESTAMP     NULL DEFAULT current_timestamp(),
  `updated_at`            TIMESTAMP     NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_uuid` (`order_uuid`),
  KEY `site_id`     (`site_id`),
  KEY `vehicle_id`  (`vehicle_id`),
  KEY `tracking_id` (`tracking_id`),
  KEY `contract_id` (`contract_id`),
  KEY `status`      (`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ════════════════════════════════════════════════════════════════════════════
--  5. CONTRACTS — contractele și facturile generate din admin
-- ════════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `contracts` (
  `id`            INT(11)      NOT NULL AUTO_INCREMENT,
  `site_id`       INT(11)      NOT NULL,
  `order_id`      INT(11)      NOT NULL,
  `doc_type`      VARCHAR(20)  NOT NULL DEFAULT 'contract',   -- contract | invoice
  `document_ref`  VARCHAR(60)  NOT NULL,
  `file_path`     VARCHAR(255) DEFAULT NULL,
  `html_content`  LONGTEXT     DEFAULT NULL,
  `email_status`  VARCHAR(30)  NOT NULL DEFAULT 'pending',
  `sent_at`       DATETIME     DEFAULT NULL,
  `created_at`    TIMESTAMP    NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `order_id`     (`order_id`),
  KEY `site_id`      (`site_id`),
  KEY `document_ref` (`document_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ════════════════════════════════════════════════════════════════════════════
--  6. SITE_CATEGORIES — categoriile de inventar, editabile per tip de site
-- ════════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `site_categories` (
  `id`          INT(11)     NOT NULL AUTO_INCREMENT,
  `site_type`   ENUM('cars','rv','heavy') NOT NULL,
  `parent_key`  VARCHAR(80) DEFAULT NULL,   -- NULL = categorie, altfel subcategorie
  `key_name`    VARCHAR(80) NOT NULL,
  `label`       VARCHAR(120) NOT NULL,
  `sort_order`  INT(11)     NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`site_type`,`parent_key`,`key_name`),
  KEY `by_type` (`site_type`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `site_categories` (`site_type`,`parent_key`,`key_name`,`label`,`sort_order`) VALUES
-- ── CARS ───────────────────────────────────────────────────────────────
('cars', NULL,        'modern',           'Modern Automobiles',  1),
('cars', 'modern',    'sedan',            'Sedan',               1),
('cars', 'modern',    'suv',              'SUV & Crossover',     2),
('cars', 'modern',    'truck',            'Truck & Pickup',      3),
('cars', 'modern',    'coupe',            'Coupé',               4),
('cars', 'modern',    'convertible',      'Convertible',         5),
('cars', 'modern',    'van',              'Van & Minivan',       6),
('cars', NULL,        'collector',        'Classic & Collector', 2),
('cars', 'collector', 'american-classic', 'American Classic',    1),
('cars', 'collector', 'european-classic', 'European Classic',    2),
('cars', 'collector', 'muscle-car',       'Muscle Car',          3),
('cars', 'collector', 'roadster',         'Roadster',            4),
('cars', 'collector', 'restomod',         'Restomod',            5),
('cars', 'collector', 'barn-find',        'Barn Find & Project', 6),
-- ── RV ─────────────────────────────────────────────────────────────────
('rv',   NULL,        'motorhomes',       'Motorhomes',          1),
('rv',   'motorhomes','class-a',          'Class A Motorhome',   1),
('rv',   'motorhomes','class-b',          'Class B Camper Van',  2),
('rv',   'motorhomes','class-c',          'Class C Motorhome',   3),
('rv',   'motorhomes','super-c',          'Super C Motorhome',   4),
('rv',   NULL,        'towables',         'Towable RVs',         2),
('rv',   'towables',  'travel-trailer',   'Travel Trailer',      1),
('rv',   'towables',  'fifth-wheel',      'Fifth Wheel',         2),
('rv',   'towables',  'toy-hauler',       'Toy Hauler',          3),
('rv',   'towables',  'pop-up',           'Pop-Up Camper',       4),
('rv',   'towables',  'teardrop',         'Teardrop Trailer',    5),
('rv',   'towables',  'truck-camper',     'Truck Camper',        6),
-- ── HEAVY (pregătit pentru al treilea site) ────────────────────────────
('heavy', NULL,       'farm',             'Farm Equipment',      1),
('heavy', 'farm',     'tractors',         'Tractors',            1),
('heavy', 'farm',     'combines',         'Combines',            2),
('heavy', 'farm',     'sprayers',         'Sprayers',            3),
('heavy', 'farm',     'hay-forage',       'Hay & Forage',        4),
('heavy', NULL,       'construction',     'Construction Equipment', 2),
('heavy', 'construction','excavators',    'Excavators',          1),
('heavy', 'construction','skid-steers',   'Skid Steers',         2),
('heavy', 'construction','backhoes',      'Backhoes',            3),
('heavy', 'construction','wheel-loaders', 'Wheel Loaders',       4),
('heavy', 'construction','dozers',        'Dozers',              5),
('heavy', 'construction','telehandlers',  'Telehandlers',        6);

-- ════════════════════════════════════════════════════════════════════════════
--  GATA. Verifică în phpMyAdmin că tabela `sites` are coloana `site_type`
--  și că `vehicles` are `price_total` — atunci migrația a reușit.
-- ════════════════════════════════════════════════════════════════════════════

-- ════════════════════════════════════════════════════════════════════════════
--  PARTEA 3 — contul de administrator
--  ──────────────────────────────────────────────────────────────────────────
--  Email    : admin@adminkartisistem.com
--  Parolă   : Kw50e1593c02!7
--
--  SCHIMB-O la prima autentificare: Admin → Settings → Change password.
--  După ce ai schimbat-o, șterge fișierul ăsta de pe server (dacă l-ai urcat).
-- ════════════════════════════════════════════════════════════════════════════

INSERT INTO `users` (`site_id`, `role`, `name`, `email`, `password_hash`, `is_active`)
VALUES (NULL, 'super_admin', 'Administrator', 'admin@adminkartisistem.com',
        '$2y$12$bMsdtfbRJcQxPK3e1a7SheaTt5YTDi6gdJiCQKKtTUVPiFRSAyLdy', 1);