VIEW, protsedura va triggerlar

CREATE VIEW talaba_royxati AS
SELECT
    t.id,
    CONCAT(t.ism, ' ', t.familiya) AS toliq_ism,
    t.email,
    t.shahar,
    t.ortacha_baho,
    g.nomi                          AS guruh
FROM talabalar t
LEFT JOIN guruhlar g ON g.id = t.guruh_id
WHERE t.faolmi = TRUE;

SELECT * FROM talaba_royxati WHERE shahar = 'Toshkent';

SELECT guruh, COUNT(*) FROM talaba_royxati GROUP BY guruh;

-- Maosh ustunini yashiramiz
CREATE VIEW xodimlar_ochiq AS
SELECT id, ism, lavozim, bolim
FROM xodimlar;

-- Foydalanuvchiga faqat VIEW ga ruxsat beramiz
GRANT SELECT ON kompaniya.xodimlar_ochiq TO 'oddiy_xodim'@'localhost';

-- O'zgartirish mumkin bo'lgan VIEW
CREATE VIEW faol_talabalar AS
SELECT id, ism, familiya, email, faolmi
FROM talabalar
WHERE faolmi = TRUE
WITH CHECK OPTION;

UPDATE faol_talabalar SET ism = 'Yangi' WHERE id = 1;   -- ishlaydi

DELIMITER //

CREATE PROCEDURE guruh_statistikasi(IN p_guruh_id INT UNSIGNED)
BEGIN
    SELECT
        g.nomi                          AS guruh,
        COUNT(t.id)                     AS talabalar,
        ROUND(AVG(t.ortacha_baho), 2)   AS ortacha,
        MAX(t.ortacha_baho)             AS eng_yuqori
    FROM guruhlar g
    LEFT JOIN talabalar t ON t.guruh_id = g.id
    WHERE g.id = p_guruh_id
    GROUP BY g.id, g.nomi;
END //

DELIMITER ;

CALL guruh_statistikasi(1);

DELIMITER //

CREATE PROCEDURE pul_otkazish(
    IN  p_kimdan   INT UNSIGNED,
    IN  p_kimga    INT UNSIGNED,
    IN  p_summa    DECIMAL(12,2),
    OUT p_natija   VARCHAR(100)
)
BEGIN
    DECLARE v_balans DECIMAL(12,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_natija = 'Xatolik yuz berdi';
    END;

    START TRANSACTION;

    SELECT balans INTO v_balans
    FROM hisoblar WHERE id = p_kimdan FOR UPDATE;

    IF v_balans < p_summa THEN
        SET p_natija = 'Mablag'' yetarli emas';
        ROLLBACK;
    ELSE
        UPDATE hisoblar SET balans = balans - p_summa WHERE id = p_kimdan;
        UPDATE hisoblar SET balans = balans + p_summa WHERE id = p_kimga;
        SET p_natija = 'Muvaffaqiyatli';
        COMMIT;
    END IF;
END //

DELIMITER ;

CALL pul_otkazish(1, 2, 500000, @natija);
SELECT @natija;

DELIMITER //

CREATE FUNCTION harfli_baho(p_baho DECIMAL(3,2))
RETURNS VARCHAR(20)
DETERMINISTIC
READS SQL DATA
BEGIN
    IF p_baho >= 4.5 THEN RETURN 'A''lo';
    ELSEIF p_baho >= 3.5 THEN RETURN 'Yaxshi';
    ELSEIF p_baho >= 3.0 THEN RETURN 'Qoniqarli';
    ELSE RETURN 'Qoniqarsiz';
    END IF;
END //

DELIMITER ;

SELECT ism, ortacha_baho, harfli_baho(ortacha_baho) AS daraja
FROM talabalar;

DELIMITER //

CREATE PROCEDURE misol()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE v_natija VARCHAR(200) DEFAULT '';

    -- IF
    IF i > 0 THEN
        SET v_natija = 'musbat';
    ELSEIF i = 0 THEN
        SET v_natija = 'nol';
    ELSE
        SET v_natija = 'manfiy';
    END IF;

    -- CASE
    CASE i
        WHEN 1 THEN SET v_natija = 'bir';
        WHEN 2 THEN SET v_natija = 'ikki';
        ELSE SET v_natija = 'boshqa';
    END CASE;

    -- WHILE
    SET i = 1;
    WHILE i <= 5 DO
        SET v_natija = CONCAT(v_natija, i, ' ');
        SET i = i + 1;
    END WHILE;

    -- REPEAT
    SET i = 1;
    REPEAT
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;

    SELECT v_natija;
END //

DELIMITER ;

DELIMITER //

-- Talaba qo'shilganda guruh hisoblagichini oshirish
CREATE TRIGGER talaba_qoshildi
AFTER INSERT ON talabalar
FOR EACH ROW
BEGIN
    UPDATE guruhlar
    SET talabalar_soni = talabalar_soni + 1
    WHERE id = NEW.guruh_id;
END //

-- Talaba o'chirilganda kamaytirish
CREATE TRIGGER talaba_ochirildi
AFTER DELETE ON talabalar
FOR EACH ROW
BEGIN
    UPDATE guruhlar
    SET talabalar_soni = talabalar_soni - 1
    WHERE id = OLD.guruh_id;
END //

DELIMITER ;

DELIMITER //

-- 1. Ma'lumotni tekshirish
CREATE TRIGGER baho_tekshiruvi
BEFORE INSERT ON talabalar
FOR EACH ROW
BEGIN
    IF NEW.ortacha_baho < 0 OR NEW.ortacha_baho > 5 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Baho 0 va 5 orasida bo''lishi kerak';
    END IF;

    SET NEW.email = LOWER(TRIM(NEW.email));      -- normalizatsiya
END //

-- 2. O'zgarishlar tarixini yozish (audit)
CREATE TRIGGER narx_ozgarishi
AFTER UPDATE ON mahsulotlar
FOR EACH ROW
BEGIN
    IF OLD.narx <> NEW.narx THEN
        INSERT INTO narx_tarixi (mahsulot_id, eski_narx, yangi_narx, ozgartirilgan)
        VALUES (NEW.id, OLD.narx, NEW.narx, NOW());
    END IF;
END //

-- 3. Yumshoq o'chirishni majburlash
CREATE TRIGGER ochirishni_taqiqlash
BEFORE DELETE ON mijozlar
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Mijozni o''chirib bo''lmaydi. faolmi = FALSE qiling.';
END //

DELIMITER ;

SHOW TRIGGERS;
DROP TRIGGER IF EXISTS talaba_qoshildi;

SET GLOBAL event_scheduler = ON;

DELIMITER //

-- Har kuni yarim tunda eski loglarni tozalash
CREATE EVENT loglarni_tozalash
ON SCHEDULE EVERY 1 DAY
STARTS '2026-09-10 00:00:00'
DO
BEGIN
    DELETE FROM loglar
    WHERE yaratilgan < DATE_SUB(NOW(), INTERVAL 90 DAY);
END //

-- Har soatda statistikani yangilash
CREATE EVENT statistikani_yangilash
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    REPLACE INTO kunlik_statistika (sana, buyurtmalar, tushum)
    SELECT CURDATE(), COUNT(*), SUM(jami)
    FROM buyurtmalar
    WHERE DATE(yaratilgan) = CURDATE();
END //

DELIMITER ;

SHOW EVENTS;
DROP EVENT IF EXISTS loglarni_tozalash;