Migratsiyalar va sxema evolyutsiyasi

CREATE TABLE migratsiyalar (
    versiya     VARCHAR(20) PRIMARY KEY,
    nomi        VARCHAR(200) NOT NULL,
    bajarilgan  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO migratsiyalar (versiya, nomi, bajarilgan) VALUES
    ('20260101_0900', 'foydalanuvchilar jadvali', '2026-01-01 09:00:00'),
    ('20260115_1430', 'maqolalar jadvali',        '2026-01-15 14:30:00'),
    ('20260203_1000', 'maqolalarga rukn qoshish', '2026-02-03 10:00:00');

SELECT versiya, nomi, bajarilgan FROM migratsiyalar ORDER BY versiya;

-- 20260210_1100_maqolalarga_korishlar_qoshish.sql
-- OLDINGA (up)

ALTER TABLE migratsiyalar ADD COLUMN izoh VARCHAR(500) NULL;

SELECT COLUMN_NAME AS ustun
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'migratsiyalar'
ORDER BY ORDINAL_POSITION;

-- ORQAGA (down) - o'zgarishni qaytarish
ALTER TABLE migratsiyalar ADD COLUMN izoh VARCHAR(500) NULL;
ALTER TABLE migratsiyalar DROP COLUMN izoh;

SELECT COUNT(*) AS ustunlar
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'migratsiyalar';

CREATE TABLE mijozlar (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    tel     VARCHAR(20) NOT NULL,
    ism     VARCHAR(100) NOT NULL
);

INSERT INTO mijozlar (tel, ism) VALUES
    ('901234567', 'Husanboy'), ('911112233', 'Malika');

-- Bir qadamda nomni o'zgartirish
ALTER TABLE mijozlar CHANGE tel telefon VARCHAR(20) NOT NULL;

SELECT id, telefon, ism FROM mijozlar ORDER BY id;

CREATE TABLE mijozlar_v2 (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    tel  VARCHAR(20) NOT NULL,
    ism  VARCHAR(100) NOT NULL
);

INSERT INTO mijozlar_v2 (tel, ism) VALUES
    ('901234567', 'Husanboy'), ('911112233', 'Malika');

-- 1-BOSQICH: kengaytirish - yangi ustun qo'shamiz
ALTER TABLE mijozlar_v2 ADD COLUMN telefon VARCHAR(20) NULL;

-- 2-BOSQICH: ma'lumotni ko'chirish
UPDATE mijozlar_v2 SET telefon = tel WHERE telefon IS NULL;

SELECT id, tel, telefon FROM mijozlar_v2 ORDER BY id;

-- (1 va 2-bosqich takrorlangan - har misol toza bazada ishlaydi)
ALTER TABLE mijozlar_v2 ADD COLUMN telefon VARCHAR(20) NULL;
UPDATE mijozlar_v2 SET telefon = tel WHERE telefon IS NULL;

-- 3-BOSQICH: eski ustunni olib tashlash (kod yangilangandan KEYIN)
ALTER TABLE mijozlar_v2 DROP COLUMN tel;
ALTER TABLE mijozlar_v2 MODIFY telefon VARCHAR(20) NOT NULL;

SELECT COLUMN_NAME AS ustun, IS_NULLABLE AS null_mumkinmi
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mijozlar_v2'
ORDER BY ORDINAL_POSITION;

SELECT
    TABLE_NAME AS jadval,
    TABLE_ROWS AS taxminiy_qatorlar,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS malumot_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mijozlar'
ORDER BY TABLE_NAME;

CREATE TABLE foydalanuvchilar (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    toliq_ism VARCHAR(200) NOT NULL
);

INSERT INTO foydalanuvchilar (toliq_ism) VALUES
    ('Husanboy Qodirov'), ('Malika Yusupova'), ('Kamola');

-- Ismni ikki ustunga ajratamiz
ALTER TABLE foydalanuvchilar
    ADD COLUMN ism      VARCHAR(100) NULL,
    ADD COLUMN familiya VARCHAR(100) NULL;

UPDATE foydalanuvchilar
SET ism      = SUBSTRING_INDEX(toliq_ism, ' ', 1),
    familiya = CASE
                   WHEN LOCATE(' ', toliq_ism) > 0
                   THEN SUBSTRING(toliq_ism, LOCATE(' ', toliq_ism) + 1)
                   ELSE NULL
               END;

SELECT id, toliq_ism, ism, COALESCE(familiya, '(yoq)') AS familiya
FROM foydalanuvchilar
ORDER BY id;

-- 20260315_1400_maqolalarga_korishlar_qoshish.sql
--
-- Maqsad: maqola necha marta ko'rilganini saqlash
-- Muallif: Husanboy Qodirov
-- Xavflilik: past (yangi ustun, DEFAULT bilan)
-- Taxminiy vaqt: < 1 soniya (jadvalda ~5000 qator)

ALTER TABLE maqolalar
    ADD COLUMN korishlar INT UNSIGNED NOT NULL DEFAULT 0;

CREATE INDEX idx_maqola_korishlar ON maqolalar (korishlar DESC);

-- Tekshiruv:
--   SELECT COUNT(*) FROM maqolalar WHERE korishlar IS NULL;  -> 0 bo'lishi kerak