SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS md_clients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(50) NULL,
    type ENUM('individual','company') NOT NULL DEFAULT 'individual',
    status ENUM('prospect','active','inactive','blocked') NOT NULL DEFAULT 'active',
    name VARCHAR(190) NOT NULL,
    tax_id VARCHAR(30) NULL,
    account_code VARCHAR(50) NULL,
    trade_registry VARCHAR(50) NULL,
    supplier_group VARCHAR(100) NULL,
    branch_code VARCHAR(100) NULL,
    third_party_type VARCHAR(100) NULL,
    contact_name VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(190) NULL,
    address TEXT NULL,
    city VARCHAR(100) NULL,
    county VARCHAR(100) NULL,
    country VARCHAR(100) NOT NULL DEFAULT 'România',
    payment_term_days INT UNSIGNED NOT NULL DEFAULT 0,
    credit_limit DECIMAL(15,2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RON',
    notes TEXT NULL,
    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 uq_md_clients_code (code),
    KEY idx_md_clients_name (name),
    KEY idx_md_clients_tax_id (tax_id),
    KEY idx_md_clients_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_suppliers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(50) NULL,
    status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
    name VARCHAR(190) NOT NULL,
    tax_id VARCHAR(30) NULL,
    trade_registry VARCHAR(50) NULL,
    contact_name VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(190) NULL,
    website VARCHAR(255) NULL,
    address TEXT NULL,
    city VARCHAR(100) NULL,
    county VARCHAR(100) NULL,
    country VARCHAR(100) NOT NULL DEFAULT 'România',
    postal_code VARCHAR(20) NULL,
    saft_code VARCHAR(50) NULL,
    is_vat_payer TINYINT(1) NOT NULL DEFAULT 1,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    import_type VARCHAR(100) NULL,
    vat_registration_date DATE NULL,
    vat_end_date DATE NULL,
    vat_validation_date DATE NULL,
    vat_suspension_date DATE NULL,
    additional_info TEXT NULL,
    bank_name VARCHAR(190) NULL,
    iban VARCHAR(50) NULL,
    payment_term_days INT UNSIGNED NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RON',
    default_discount DECIMAL(7,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    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 uq_md_suppliers_code (code),
    KEY idx_md_suppliers_name (name),
    KEY idx_md_suppliers_tax_id (tax_id),
    KEY idx_md_suppliers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_manufacturers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(190) NOT NULL,
    short_name VARCHAR(100) NULL,
    manufacturer_type ENUM('oe','oem','aftermarket','reconditioned') NOT NULL DEFAULT 'aftermarket',
    country VARCHAR(100) NULL,
    website VARCHAR(255) NULL,
    logo_path VARCHAR(500) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    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 uq_md_manufacturers_name (name),
    KEY idx_md_manufacturers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_product_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id BIGINT UNSIGNED NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    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 uq_md_categories_parent_name (parent_id, name),
    KEY idx_md_categories_status (status),
    CONSTRAINT fk_md_categories_parent
        FOREIGN KEY (parent_id) REFERENCES md_product_categories(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_units (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    decimals TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    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 uq_md_units_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_products (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(100) NOT NULL,
    oe_code VARCHAR(190) NULL,
    manufacturer_code VARCHAR(190) NULL,
    ean VARCHAR(50) NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    category_id BIGINT UNSIGNED NULL,
    manufacturer_id BIGINT UNSIGNED NULL,
    unit_id BIGINT UNSIGNED NULL,
    vat_rate DECIMAL(6,2) NOT NULL DEFAULT 21.00,
    purchase_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    sale_price_without_vat DECIMAL(15,2) NOT NULL DEFAULT 0,
    sale_price_with_vat DECIMAL(15,2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RON',
    stock_quantity DECIMAL(15,3) NOT NULL DEFAULT 0,
    minimum_stock DECIMAL(15,3) NOT NULL DEFAULT 0,
    location_code VARCHAR(100) NULL,
    warranty_months INT UNSIGNED NOT NULL DEFAULT 0,
    image_path VARCHAR(500) NULL,
    status ENUM('active','inactive','discontinued') NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    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 uq_md_products_internal_code (internal_code),
    KEY idx_md_products_oe_code (oe_code),
    KEY idx_md_products_manufacturer_code (manufacturer_code),
    KEY idx_md_products_ean (ean),
    KEY idx_md_products_name (name),
    KEY idx_md_products_status (status),
    CONSTRAINT fk_md_products_category
        FOREIGN KEY (category_id) REFERENCES md_product_categories(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_md_products_manufacturer
        FOREIGN KEY (manufacturer_id) REFERENCES md_manufacturers(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_md_products_unit
        FOREIGN KEY (unit_id) REFERENCES md_units(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS md_supplier_products (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    supplier_code VARCHAR(190) NOT NULL,
    purchase_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RON',
    supplier_stock VARCHAR(100) NULL,
    delivery_days INT UNSIGNED NOT NULL DEFAULT 0,
    is_preferred TINYINT(1) NOT NULL DEFAULT 0,
    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 uq_md_supplier_product_code (supplier_id, supplier_code),
    KEY idx_md_supplier_products_product (product_id),
    CONSTRAINT fk_md_supplier_products_product
        FOREIGN KEY (product_id) REFERENCES md_products(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_md_supplier_products_supplier
        FOREIGN KEY (supplier_id) REFERENCES md_suppliers(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO md_units (code, name, decimals, status) VALUES
('BUC', 'Bucată', 0, 'active'),
('SET', 'Set', 0, 'active'),
('L', 'Litru', 3, 'active'),
('KG', 'Kilogram', 3, 'active'),
('M', 'Metru', 2, 'active');

INSERT IGNORE INTO md_product_categories (name, sort_order, status) VALUES
('Frânare', 10, 'active'),
('Distribuție', 20, 'active'),
('Răcire motor', 30, 'active'),
('Motor', 40, 'active'),
('Aprindere', 50, 'active'),
('Alimentare combustibil', 60, 'active'),
('Evacuare', 70, 'active'),
('Ambreiaj', 80, 'active'),
('Cutie de viteze', 90, 'active'),
('Transmisie', 100, 'active'),
('Direcție', 110, 'active'),
('Suspensie', 120, 'active'),
('Electrică', 130, 'active'),
('Alternator și pornire', 140, 'active'),
('Acumulator', 150, 'active'),
('Iluminare', 160, 'active'),
('Climatizare / A/C', 170, 'active'),
('Filtrare', 180, 'active'),
('Lubrifiere', 190, 'active'),
('Caroserie', 200, 'active'),
('Senzori și electronică', 210, 'active'),
('Roți și anvelope', 220, 'active'),
('Consumabile', 230, 'active'),
('Accesorii', 240, 'active'),
('Altele', 999, 'active');

INSERT IGNORE INTO md_manufacturers
(name, short_name, manufacturer_type, status) VALUES
('BOSCH', 'BOSCH', 'aftermarket', 'active'),
('SKF', 'SKF', 'aftermarket', 'active'),
('CONTINENTAL', 'CONTINENTAL', 'aftermarket', 'active'),
('VALEO', 'VALEO', 'aftermarket', 'active'),
('TRW', 'TRW', 'aftermarket', 'active'),
('BREMBO', 'BREMBO', 'aftermarket', 'active'),
('MONROE', 'MONROE', 'aftermarket', 'active'),
('FEBI BILSTEIN', 'FEBI', 'aftermarket', 'active'),
('DELPHI', 'DELPHI', 'aftermarket', 'active'),
('OE FORD', 'FORD', 'oe', 'active'),
('OE VOLKSWAGEN', 'VW', 'oe', 'active'),
('OE BMW', 'BMW', 'oe', 'active'),
('OE MERCEDES-BENZ', 'MERCEDES', 'oe', 'active');

SET FOREIGN_KEY_CHECKS = 1;