-- ============================================================
-- MechArmenia — Մեքենաշինական Կայքի Տվյալների Բազա
-- Նորմալիզացված 3NF կառուցվածք
-- ============================================================

CREATE DATABASE IF NOT EXISTS mecharmenia
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE mecharmenia;

-- ── 1. ՃՅՈՒՂԵՐ (Branches) ──────────────────────────────────
CREATE TABLE branches (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(30) UNIQUE NOT NULL,
    name_hy     VARCHAR(120) NOT NULL,
    description TEXT,
    image_url   VARCHAR(255),
    sort_order  INT DEFAULT 0
) ENGINE=InnoDB;

-- ── 2. ՄԵՔԵՆԱՏԵՍԱԿՆԵՐ (Machine Types) ──────────────────────
CREATE TABLE machine_types (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    branch_id   INT NOT NULL,
    name_hy     VARCHAR(150) NOT NULL,
    description TEXT,
    image_url   VARCHAR(255),
    year_intro  INT,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 3. ՄԱՍԵՐԻ ԿԱՏԵԳՈՐԻԱՆԵՐ (Part Categories) ───────────────
CREATE TABLE part_categories (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    name_hy   VARCHAR(100) NOT NULL,
    icon      VARCHAR(10) DEFAULT '⚙'
) ENGINE=InnoDB;

-- ── 4. ՄԱՍԵՐ (Parts) ────────────────────────────────────────
CREATE TABLE parts (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    branch_id    INT NOT NULL,
    category_id  INT NOT NULL,
    article      VARCHAR(60) UNIQUE NOT NULL,
    name_hy      VARCHAR(150) NOT NULL,
    material     VARCHAR(100),
    weight_kg    DECIMAL(10,3),
    unit         VARCHAR(20) DEFAULT 'հատ',
    price_amd    DECIMAL(12,2),
    stock_qty    INT DEFAULT 0,
    description  TEXT,
    image_url    VARCHAR(255),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id)   REFERENCES branches(id)         ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES part_categories(id)  ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ── 5. ՄԵՔԵՆԱ–ՄԱՇ ԿԱՊԸ (Machine–Part relation) ─────────────
CREATE TABLE machine_parts (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    machine_type_id INT NOT NULL,
    part_id         INT NOT NULL,
    quantity_needed  INT NOT NULL DEFAULT 1,
    is_critical      TINYINT(1) DEFAULT 0,
    notes           VARCHAR(255),
    UNIQUE KEY uq_mp (machine_type_id, part_id),
    FOREIGN KEY (machine_type_id) REFERENCES machine_types(id) ON DELETE CASCADE,
    FOREIGN KEY (part_id)         REFERENCES parts(id)          ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 6. ՀԱՅԿԱԿԱՆ ԳՈՐԾԱՐԱՆՆԵՐ ─────────────────────────────────
CREATE TABLE factories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name_hy     VARCHAR(150) NOT NULL,
    city        VARCHAR(80),
    founded     INT,
    employees   INT,
    products    TEXT,
    image_url   VARCHAR(255),
    website     VARCHAR(200),
    description TEXT
) ENGINE=InnoDB;

-- ── 7. ՄԱՍՆԱԳԻՏՈՒԹՅՈՒՆՆԵՐ ───────────────────────────────────
CREATE TABLE professions (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    title_hy     VARCHAR(120) NOT NULL,
    description  TEXT,
    salary_range VARCHAR(80),
    skills       TEXT,
    image_url    VARCHAR(255)
) ENGINE=InnoDB;

-- ── 8. ԿԱՊ ՀԱՂՈՐԴԱԳՐՈՒԹՅՈՒՆՆԵՐ ──────────────────────────────
CREATE TABLE contact_messages (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL,
    subject    VARCHAR(200),
    message    TEXT NOT NULL,
    is_read    TINYINT(1) DEFAULT 0,
    sent_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── 9. ՀԱՇՎԵՏՎՈՒԹՅՈՒՆՆԵՐ LOG ────────────────────────────────
CREATE TABLE query_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    query_name  VARCHAR(100),
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ==============================================================
-- SEED DATA
-- ==============================================================

INSERT INTO branches (code, name_hy, description, image_url, sort_order) VALUES
('auto',   'Ավտոմոբիլաշինություն',       'Մարդատար, բեռնատար և հատուկ նշանակության ավտոմոբիլների արտադրություն', 'https://images.unsplash.com/photo-1503376780353-7e6692767b70?w=800&q=80', 1),
('agri',   'Գյուղատնտեսական մեքենաներ',  'Տրակտորներ, կոմբայններ, ցանքամեքենաներ', 'https://images.unsplash.com/photo-1601584115197-04ecc0da31d7?w=800&q=80', 2),
('robot',  'Ռոբոտաշինություն',            'Արդյունաբերական ռոբոտներ, CNC մեքենաներ, ավտոմատացում', 'https://images.unsplash.com/photo-1485827404703-89b55fcc595e?w=800&q=80', 3),
('mach',   'Հաստոցաշինություն',           'Հաստոցներ, մամիկներ, կտրող գործիքներ', 'https://images.unsplash.com/photo-1565043666747-69f6646db940?w=800&q=80', 4);

INSERT INTO part_categories (name_hy, icon) VALUES
('Շարժիչ', '🔥'),('Փոխանցատուփ', '⚙'),('Կախոց', '🔩'),
('Արգելակային', '🛑'),('Էլեկտրական', '⚡'),('Հիդրավլիկ', '💧'),
('Կմախքային', '🏗'),('Ծածկ/Կաբինա', '🚪');

INSERT INTO machine_types (branch_id, name_hy, description, image_url, year_intro) VALUES
(1,'Մարդատար ավտոմոբիլ','4-անիվ, 4–5 նստատեղ, բենզինային/էլ. շարժիչ','https://images.unsplash.com/photo-1511919884226-fd3cad34687c?w=700&q=80',1886),
(1,'Բեռնատար AvtoMek','Ծանր բեռնատար, 12–40 t կրողունակ','https://images.unsplash.com/photo-1601584115197-04ecc0da31d7?w=700&q=80',1896),
(2,'Տրակտոր MTZ-82','Անիվավոր, 80 ձ.ուժ, ունիվերսալ','https://images.unsplash.com/photo-1559827260-dc66d52bef19?w=700&q=80',1952),
(2,'Կոմբայն Կ-700','Ինքնագնաց հացահատիկահավաք','https://images.unsplash.com/photo-1574323347407-f5e1c0cf4b44?w=700&q=80',1969),
(3,'Արդ. ռոբոտ ABB IRB','6 ազատության աստիճան, 10 kg կրողունակ','https://images.unsplash.com/photo-1563986768609-322da13575f3?w=700&q=80',2005),
(3,'CNC Ֆրեզ DMG','3–5 առանցք, 0.001 mm ճշտ.','https://images.unsplash.com/photo-1565043666747-69f6646db940?w=700&q=80',1990),
(4,'Հաստոց 1M63','Հարդարամշակ, Ø500 mm','https://images.unsplash.com/photo-1504917595217-d4dc5ebe6122?w=700&q=80',1963),
(4,'Ֆրեզ 6R82','Հորիզ. ֆրեզ 320 mm','https://images.unsplash.com/photo-1581091226825-a6a2a5aee158?w=700&q=80',1975);

INSERT INTO parts (branch_id, category_id, article, name_hy, material, weight_kg, unit, price_amd, stock_qty, description, image_url) VALUES
(1,1,'AUT-ENG-001','Բենզինային շարժիչ 2.0L','Ալյումին/Պողպատ',180.0,'հատ',850000,5,'4 մխոց, DOHC, 150 ձ.ուժ','https://images.unsplash.com/photo-1486262715619-67b85e0b08d3?w=400&q=80'),
(1,2,'AUT-GBX-001','Ավտոմ. փոխ. 6-աստ.','Պողպատ/Ալ.',65.0,'հատ',420000,8,'AT, Torque Converter','https://images.unsplash.com/photo-1599256621730-535171e28e50?w=400&q=80'),
(1,3,'AUT-SUS-001','Ռեսոր MacPherson','Պողպատ 60С2А',4.5,'հատ',35000,40,'Ճակատային, D=50mm','https://images.unsplash.com/photo-1449426468159-d96dbf08f19f?w=400&q=80'),
(1,4,'AUT-BRK-001','Արգ. բաժ. Brembo','Չուգուն GG25',8.2,'հատ',28000,60,'D=280 mm, ծակ.','https://images.unsplash.com/photo-1558618666-fcd25c85cd64?w=400&q=80'),
(1,5,'AUT-ALT-001','Ալternator 120A','Պղինձ/Ալ.',5.8,'հատ',45000,25,'12V, 120A, Bosch','https://images.unsplash.com/photo-1486262715619-67b85e0b08d3?w=400&q=80'),
(2,1,'AGR-ENG-001','Diesel 4-մխոց 80ձ.','Ձուլ. Պողp.',320.0,'հատ',1200000,3,'MTZ D-243, 80 hp','https://images.unsplash.com/photo-1543674892-7d64d45df18b?w=400&q=80'),
(2,6,'AGR-HYD-001','Հիդ. պոմպ NSh-32','Ալ. Ձուլ.',8.7,'հատ',65000,15,'32 cc/rev, 200 bar','https://images.unsplash.com/photo-1504917595217-d4dc5ebe6122?w=400&q=80'),
(2,7,'AGR-FRM-001','Շրջանակ հիմ.','St3, Եռակ.',420.0,'հատ',380000,2,'6×2.4 m, 6mm t.','https://images.unsplash.com/photo-1581091226825-a6a2a5aee158?w=400&q=80'),
(3,5,'ROB-MOT-001','Servo Motor 1kW','Cu/Fe',4.2,'հատ',180000,20,'1000W, AC, encoder','https://images.unsplash.com/photo-1563986768609-322da13575f3?w=400&q=80'),
(3,5,'ROB-CTL-001','PLC Siemens S7','Al/PCB',1.1,'հատ',320000,10,'S7-1500, 24VDC','https://images.unsplash.com/photo-1518770660439-4636190af475?w=400&q=80'),
(3,2,'ROB-GBX-001','Harmonic Drive','Cr-Mo St.',2.3,'հատ',250000,15,'100:1, 0.01 arcmin','https://images.unsplash.com/photo-1565043666747-69f6646db940?w=400&q=80'),
(4,7,'MCH-BED-001','Հաստոց Մahkag','ՉՈ-250',1200.0,'հատ',2500000,1,'L=3m, W=0.8m','https://images.unsplash.com/photo-1504917595217-d4dc5ebe6122?w=400&q=80'),
(4,2,'MCH-SPD-001','Գ/արկղ 12-աst.','St45',85.0,'հատ',320000,4,'n=50–2000 rpm','https://images.unsplash.com/photo-1486262715619-67b85e0b08d3?w=400&q=80'),
(4,1,'MCH-MTR-001','Asinkron 7.5kW','Cu/Fe',62.0,'հատ',145000,8,'7.5kW, 1500rpm','https://images.unsplash.com/photo-1581091226825-a6a2a5aee158?w=400&q=80');

INSERT INTO machine_parts (machine_type_id, part_id, quantity_needed, is_critical, notes) VALUES
(1,1,1,1,'Հիմ. շարժ.'),(1,2,1,1,'Փոխ.'),(1,3,4,0,'4 անիվ'),(1,4,4,1,'Անht.'),(1,5,1,0,'Լcn.'),
(3,6,1,1,'Հիմ.'),(3,7,2,0,'Ճakt.'),(3,8,1,1,'Կmakhk.'),
(5,9,6,1,'6 shaft'),(5,10,1,1,'Ctrl'),(5,11,6,1,'Reduk.'),
(7,12,1,1,'Կgh.'),(7,13,1,1,'Փkh.'),(7,14,1,0,'Mtr.');

INSERT INTO factories (name_hy, city, founded, employees, products, image_url, website, description) VALUES
('Արզնի Գործարան','Արզնի',1948,850,'Ճշգրիտ մեքենամասեր, ռազմական տեխ.','https://images.unsplash.com/photo-1565043666747-69f6646db940?w=600&q=80','arzni.am','Հայաստանի հնագույն ճshgrit mshakum gorcaran'),
('Avan Engineering','Երևան',2005,320,'CNC մshakum, avia մasner','https://images.unsplash.com/photo-1504917595217-d4dc5ebe6122?w=600&q=80','avan-eng.am','Aaviacion ev bzhshk. olorty'),
('ՀայՄաշ ՍՊԸ','Գյումրի',1965,1200,'Tarber masner, ktrich gorciqner','https://images.unsplash.com/photo-1581091226825-a6a2a5aee158?w=600&q=80','haymash.am','Հtk. mexaniq. gorcaran'),
('Nairi Metal Works','Երևան',2010,180,'3D print, preciz. masner','https://images.unsplash.com/photo-1563986768609-322da13575f3?w=600&q=80','nairi-metal.am','Ardict. nanotexnologiner');

INSERT INTO professions (title_hy, description, salary_range, skills, image_url) VALUES

('Մեխանիկական ինժեներ',
'Մեքենայական համակարգերի նախագծում, հաշվարկներ և արտադրություն',
'400,000–900,000 ֏',
'CAD/CAM, SolidWorks, Matlab, Դինամիկա',
'https://images.unsplash.com/photo-1581092918056-0c4c3acd3789?w=500&q=80'),

('Կոնստրուկտոր',
'Մասերի և հանգույցների նախագծում, տեխնիկական դոկումենտացիա',
'350,000–800,000 ֏',
'AutoCAD, CATIA, Tolerancing, GD&T',
'https://images.unsplash.com/photo-1537368910025-700350fe46c7?w=500&q=80'),

('Տեխնոլոգ',
'Արտադրության գործընթացների մշակում և կազմակերպման վերահսկում',
'380,000–750,000 ֏',
'CNC ծրագրավորում, ռեժիմների պարամետրեր, QC',
'https://images.unsplash.com/photo-1504917595217-d4dc5ebe6122?w=500&q=80'),

('Ավտոմատացման մասնագետ',
'PLC, ռոբոտներ, արդյունաբերական համակարգերի ծրագրավորում և գործարկում',
'500,000–1,200,000 ֏',
'Siemens, ABB, Python, ROS, PLC',
'https://images.unsplash.com/photo-1563986768609-322da13575f3?w=500&q=80');
-- ── VIEWS ──────────────────────────────────────────────────
CREATE OR REPLACE VIEW v_machine_parts_full AS
SELECT
    mt.name_hy  AS maqina,
    b.name_hy   AS chyugh,
    p.article,
    p.name_hy   AS mas,
    pc.name_hy  AS kategoria,
    mp.quantity_needed,
    mp.is_critical,
    p.price_amd,
    p.stock_qty
FROM machine_parts mp
JOIN machine_types  mt ON mp.machine_type_id = mt.id
JOIN branches        b  ON mt.branch_id       =  b.id
JOIN parts           p  ON mp.part_id         =  p.id
JOIN part_categories pc ON p.category_id      = pc.id;

CREATE OR REPLACE VIEW v_branch_stats AS
SELECT
    b.id, b.name_hy,
    COUNT(DISTINCT mt.id) AS machine_count,
    COUNT(DISTINCT p.id)  AS part_count,
    COALESCE(SUM(p.price_amd * p.stock_qty),0) AS total_stock_value
FROM branches b
LEFT JOIN machine_types mt ON mt.branch_id = b.id
LEFT JOIN parts          p  ON  p.branch_id = b.id
GROUP BY b.id, b.name_hy;
