Amaliy loyiha - onlayn do'kon bazasi

DROP DATABASE IF EXISTS dokon_loyiha;
CREATE DATABASE dokon_loyiha
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE dokon_loyiha;

CREATE TABLE kategoriyalar (
    id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ota_id INT UNSIGNED NULL,
    nomi   VARCHAR(120) NOT NULL,
    slug   VARCHAR(120) NOT NULL,
    tartib SMALLINT UNSIGNED NOT NULL DEFAULT 0,

    UNIQUE KEY uq_slug (slug),
    KEY idx_ota (ota_id),

    CONSTRAINT fk_kategoriya_ota
        FOREIGN KEY (ota_id) REFERENCES kategoriyalar(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mahsulotlar (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kategoriya_id INT UNSIGNED NOT NULL,
    nomi          VARCHAR(200) NOT NULL,
    slug          VARCHAR(200) NOT NULL,
    tavsif        TEXT NULL,
    narx          DECIMAL(12,2) NOT NULL,
    eski_narx     DECIMAL(12,2) NULL,
    zaxira        INT NOT NULL DEFAULT 0,
    faol          BOOLEAN NOT NULL DEFAULT TRUE,
    yaratilgan    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    yangilangan   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_slug (slug),
    KEY idx_kategoriya_faol (kategoriya_id, faol),
    KEY idx_narx (narx),
    FULLTEXT KEY ft_qidiruv (nomi, tavsif),

    CONSTRAINT fk_mahsulot_kategoriya
        FOREIGN KEY (kategoriya_id) REFERENCES kategoriyalar(id)
        ON DELETE RESTRICT,

    CONSTRAINT chk_narx   CHECK (narx > 0),
    CONSTRAINT chk_zaxira CHECK (zaxira >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rasmlar (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mahsulot_id INT UNSIGNED NOT NULL,
    yol         VARCHAR(255) NOT NULL,
    tartib      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    asosiy      BOOLEAN NOT NULL DEFAULT FALSE,

    KEY idx_mahsulot (mahsulot_id, tartib),

    CONSTRAINT fk_rasm_mahsulot
        FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mijozlar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism        VARCHAR(80) NOT NULL,
    familiya   VARCHAR(80) NOT NULL,
    email      VARCHAR(150) NOT NULL,
    telefon    VARCHAR(20) NULL,
    parol_xesh VARCHAR(255) NOT NULL,
    faol       BOOLEAN NOT NULL DEFAULT TRUE,
    royxatdan  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE manzillar (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id INT UNSIGNED NOT NULL,
    viloyat  VARCHAR(60) NOT NULL,
    shahar   VARCHAR(60) NOT NULL,
    kocha    VARCHAR(200) NOT NULL,
    indeks   VARCHAR(10) NULL,
    asosiy   BOOLEAN NOT NULL DEFAULT FALSE,

    KEY idx_mijoz (mijoz_id),

    CONSTRAINT fk_manzil_mijoz
        FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE buyurtmalar (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    raqam           VARCHAR(20) NOT NULL,
    mijoz_id        INT UNSIGNED NOT NULL,
    manzil_id       INT UNSIGNED NULL,
    holat           ENUM('yangi','tolangan','yigilmoqda','yolda','yetkazilgan','bekor')
                    NOT NULL DEFAULT 'yangi',
    mahsulot_summa  DECIMAL(12,2) NOT NULL DEFAULT 0,
    yetkazish_narxi DECIMAL(12,2) NOT NULL DEFAULT 0,
    jami            DECIMAL(12,2) NOT NULL DEFAULT 0,
    izoh            VARCHAR(500) NULL,
    yaratilgan      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_raqam (raqam),
    KEY idx_mijoz_sana (mijoz_id, yaratilgan),
    KEY idx_holat (holat),

    CONSTRAINT fk_buyurtma_mijoz
        FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_buyurtma_manzil
        FOREIGN KEY (manzil_id) REFERENCES manzillar(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE buyurtma_elementlari (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyurtma_id   INT UNSIGNED NOT NULL,
    mahsulot_id   INT UNSIGNED NOT NULL,
    mahsulot_nomi VARCHAR(200) NOT NULL,
    narx          DECIMAL(12,2) NOT NULL,
    soni          SMALLINT UNSIGNED NOT NULL,

    KEY idx_buyurtma (buyurtma_id),
    KEY idx_mahsulot (mahsulot_id),

    CONSTRAINT fk_element_buyurtma
        FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_element_mahsulot
        FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id)
        ON DELETE RESTRICT,

    CONSTRAINT chk_soni CHECK (soni > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sharhlar (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mahsulot_id  INT UNSIGNED NOT NULL,
    mijoz_id     INT UNSIGNED NOT NULL,
    baho         TINYINT UNSIGNED NOT NULL,
    matn         TEXT NULL,
    tasdiqlangan BOOLEAN NOT NULL DEFAULT FALSE,
    yaratilgan   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_mijoz_mahsulot (mijoz_id, mahsulot_id),
    KEY idx_mahsulot_tasdiq (mahsulot_id, tasdiqlangan),

    CONSTRAINT fk_sharh_mahsulot
        FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id) ON DELETE CASCADE,
    CONSTRAINT fk_sharh_mijoz
        FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id) ON DELETE CASCADE,

    CONSTRAINT chk_baho CHECK (baho BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holat_tarixi (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyurtma_id INT UNSIGNED NOT NULL,
    eski_holat  VARCHAR(20) NULL,
    yangi_holat VARCHAR(20) NOT NULL,
    ozgartirdi  VARCHAR(80) NULL,
    vaqt        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    KEY idx_buyurtma_vaqt (buyurtma_id, vaqt),

    CONSTRAINT fk_tarix_buyurtma
        FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO kategoriyalar (ota_id, nomi, slug, tartib) VALUES
(NULL, 'Elektronika',  'elektronika',  1),
(NULL, 'Kiyim',        'kiyim',        2),
(1,    'Kompyuterlar', 'kompyuterlar', 1),
(1,    'Telefonlar',   'telefonlar',   2),
(3,    'Noutbuklar',   'noutbuklar',   1);

INSERT INTO mahsulotlar (kategoriya_id, nomi, slug, narx, zaxira, tavsif) VALUES
(5, 'Lenovo IdeaPad 3',   'lenovo-ideapad-3', 6500000, 12, 'Kundalik ish uchun qulay noutbuk'),
(5, 'HP Pavilion 15',     'hp-pavilion-15',   8200000,  5, 'Kuchli protsessor va katta ekran'),
(4, 'Samsung Galaxy A54', 'samsung-a54',      4300000, 30, 'Yaxshi kamera, uzoq batareya'),
(4, 'Xiaomi Redmi 13',    'xiaomi-redmi-13',  2100000, 45, 'Arzon va ishonchli smartfon'),
(3, 'Klaviatura Logitech','klaviatura-logi',   250000, 80, 'Simsiz klaviatura');

INSERT INTO mijozlar (ism, familiya, email, telefon, parol_xesh) VALUES
('Husanboy', 'Qodirov',  '[email protected]', '+998901112233', '$2y$10$xeshnamunasi1'),
('Jasur',   'Toshmatov', '[email protected]',   '+998902223344', '$2y$10$xeshnamunasi2'),
('Malika',  'Yusupova',  '[email protected]',  '+998903334455', '$2y$10$xeshnamunasi3');

INSERT INTO manzillar (mijoz_id, viloyat, shahar, kocha, asosiy) VALUES
(1, 'Toshkent',  'Toshkent',  'Amir Temur ko''chasi, 15', TRUE),
(2, 'Samarqand', 'Samarqand', 'Registon ko''chasi, 7',    TRUE),
(3, 'Farg''ona', 'Qo''qon',   'Istiqlol ko''chasi, 22',   TRUE);

DELIMITER //

CREATE PROCEDURE buyurtma_yaratish(
    IN  p_mijoz_id     INT UNSIGNED,
    IN  p_manzil_id    INT UNSIGNED,
    IN  p_mahsulot_id  INT UNSIGNED,
    IN  p_soni         SMALLINT UNSIGNED,
    OUT p_buyurtma_id  INT UNSIGNED
)
BEGIN
    DECLARE v_narx   DECIMAL(12,2);
    DECLARE v_nomi   VARCHAR(200);
    DECLARE v_zaxira INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT narx, nomi, zaxira INTO v_narx, v_nomi, v_zaxira
    FROM mahsulotlar
    WHERE id = p_mahsulot_id AND faol = TRUE
    FOR UPDATE;

    IF v_zaxira IS NULL OR v_zaxira < p_soni THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Omborda yetarli mahsulot yoq';
    END IF;

    INSERT INTO buyurtmalar (raqam, mijoz_id, manzil_id, mahsulot_summa, jami)
    VALUES (
        CONCAT('B-', DATE_FORMAT(NOW(), '%Y%m%d'), '-',
               LPAD(FLOOR(RAND() * 10000), 4, '0')),
        p_mijoz_id, p_manzil_id, v_narx * p_soni, v_narx * p_soni
    );

    SET p_buyurtma_id = LAST_INSERT_ID();

    INSERT INTO buyurtma_elementlari
        (buyurtma_id, mahsulot_id, mahsulot_nomi, narx, soni)
    VALUES
        (p_buyurtma_id, p_mahsulot_id, v_nomi, v_narx, p_soni);

    UPDATE mahsulotlar SET zaxira = zaxira - p_soni WHERE id = p_mahsulot_id;

    INSERT INTO holat_tarixi (buyurtma_id, eski_holat, yangi_holat, ozgartirdi)
    VALUES (p_buyurtma_id, NULL, 'yangi', 'tizim');

    COMMIT;
END //

DELIMITER ;

CALL buyurtma_yaratish(1, 1, 1, 2, @id);

SELECT raqam, holat, jami FROM buyurtmalar WHERE id = @id;
SELECT zaxira FROM mahsulotlar WHERE id = 1;

DELIMITER //

CREATE TRIGGER buyurtma_holati_ozgardi
AFTER UPDATE ON buyurtmalar
FOR EACH ROW
BEGIN
    IF NEW.holat <> OLD.holat THEN
        INSERT INTO holat_tarixi (buyurtma_id, eski_holat, yangi_holat)
        VALUES (NEW.id, OLD.holat, NEW.holat);
    END IF;
END //

DELIMITER ;

UPDATE buyurtmalar SET holat = 'tolangan'   WHERE id = 1;
UPDATE buyurtmalar SET holat = 'yigilmoqda' WHERE id = 1;
UPDATE buyurtmalar SET holat = 'yolda'      WHERE id = 1;

SELECT eski_holat, yangi_holat, vaqt FROM holat_tarixi WHERE buyurtma_id = 1;

SELECT
    m.id,
    m.nomi,
    m.narx,
    m.eski_narx,
    k.nomi                              AS kategoriya,
    r.yol                               AS rasm,
    ROUND(COALESCE(AVG(s.baho), 0), 1)  AS ortacha_baho,
    COUNT(s.id)                         AS sharhlar_soni,
    CASE WHEN m.zaxira > 0 THEN 'bor' ELSE 'tugagan' END AS holati
FROM mahsulotlar m
JOIN      kategoriyalar k ON k.id = m.kategoriya_id
LEFT JOIN rasmlar       r ON r.mahsulot_id = m.id AND r.asosiy = TRUE
LEFT JOIN sharhlar      s ON s.mahsulot_id = m.id AND s.tasdiqlangan = TRUE
WHERE m.faol = TRUE
GROUP BY m.id, m.nomi, m.narx, m.eski_narx, k.nomi, r.yol, m.zaxira
ORDER BY m.yaratilgan DESC
LIMIT 12;

WITH RECURSIVE daraxt AS (
    SELECT id, ota_id, nomi, 0 AS daraja, CAST(nomi AS CHAR(500)) AS yol
    FROM kategoriyalar
    WHERE ota_id IS NULL

    UNION ALL

    SELECT k.id, k.ota_id, k.nomi, d.daraja + 1,
           CONCAT(d.yol, ' > ', k.nomi)
    FROM kategoriyalar k
    JOIN daraxt d ON d.id = k.ota_id
)
SELECT CONCAT(REPEAT('    ', daraja), nomi) AS kategoriya, yol
FROM daraxt
ORDER BY yol;

-- Oylik savdo
SELECT
    DATE_FORMAT(yaratilgan, '%Y-%m') AS oy,
    COUNT(*)                         AS buyurtmalar,
    SUM(jami)                        AS tushum,
    ROUND(AVG(jami))                 AS ortacha_chek
FROM buyurtmalar
WHERE holat <> 'bekor'
GROUP BY oy
ORDER BY oy DESC;

-- Eng ko'p sotilgan 10 ta mahsulot
SELECT
    be.mahsulot_nomi,
    SUM(be.soni)           AS sotilgan,
    SUM(be.soni * be.narx) AS tushum
FROM buyurtma_elementlari be
JOIN buyurtmalar b ON b.id = be.buyurtma_id
WHERE b.holat <> 'bekor'
GROUP BY be.mahsulot_id, be.mahsulot_nomi
ORDER BY sotilgan DESC
LIMIT 10;

-- Faol mijozlar reytingi
SELECT
    CONCAT(mj.ism, ' ', mj.familiya) AS mijoz,
    COUNT(b.id)                      AS buyurtmalar,
    SUM(b.jami)                      AS jami_xarid,
    MAX(b.yaratilgan)                AS oxirgi_xarid
FROM mijozlar mj
JOIN buyurtmalar b ON b.mijoz_id = mj.id AND b.holat <> 'bekor'
GROUP BY mj.id, mj.ism, mj.familiya
HAVING jami_xarid > 1000000
ORDER BY jami_xarid DESC;

-- Zaxirasi tugayotgan mahsulotlar
SELECT nomi, zaxira
FROM mahsulotlar
WHERE faol = TRUE AND zaxira <= 5
ORDER BY zaxira;

-- Bir marta ham sotilmagan mahsulotlar
SELECT m.nomi, m.narx
FROM mahsulotlar m
LEFT JOIN buyurtma_elementlari be ON be.mahsulot_id = m.id
WHERE be.id IS NULL;

CREATE VIEW savdo_xulosasi AS
SELECT
    DATE(b.yaratilgan)   AS sana,
    COUNT(DISTINCT b.id) AS buyurtmalar,
    SUM(be.soni)         AS mahsulotlar,
    SUM(be.soni * be.narx) AS tushum
FROM buyurtmalar b
JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
WHERE b.holat <> 'bekor'
GROUP BY DATE(b.yaratilgan);

SELECT * FROM savdo_xulosasi ORDER BY sana DESC LIMIT 30;

<?php
class BuyurtmaXizmati
{
    public function __construct(private PDO $pdo) {}

    public function yarating(int $mijozId, int $manzilId, array $savat): int
    {
        if ($savat === []) {
            throw new InvalidArgumentException('Savat bo\'sh.');
        }

        $this->pdo->beginTransaction();

        try {
            $raqam = 'B-' . date('Ymd') . '-'
                   . str_pad((string) random_int(0, 9999), 4, '0', STR_PAD_LEFT);

            $stmt = $this->pdo->prepare(
                'INSERT INTO buyurtmalar (raqam, mijoz_id, manzil_id)
                 VALUES (?, ?, ?)'
            );
            $stmt->execute([$raqam, $mijozId, $manzilId]);
            $buyurtmaId = (int) $this->pdo->lastInsertId();

            $summa = $this->elementlarniQoshing($buyurtmaId, $savat);

            $stmt = $this->pdo->prepare(
                'UPDATE buyurtmalar SET mahsulot_summa = ?, jami = ? WHERE id = ?'
            );
            $stmt->execute([$summa, $summa, $buyurtmaId]);

            $this->pdo->commit();

            return $buyurtmaId;
        } catch (Throwable $xato) {
            $this->pdo->rollBack();
            throw $xato;
        }
    }

    private function elementlarniQoshing(int $buyurtmaId, array $savat): float
    {
        $olish = $this->pdo->prepare(
            'SELECT nomi, narx, zaxira FROM mahsulotlar
             WHERE id = ? AND faol = TRUE FOR UPDATE'
        );
        $qoshish = $this->pdo->prepare(
            'INSERT INTO buyurtma_elementlari
                (buyurtma_id, mahsulot_id, mahsulot_nomi, narx, soni)
             VALUES (?, ?, ?, ?, ?)'
        );
        $kamaytirish = $this->pdo->prepare(
            'UPDATE mahsulotlar SET zaxira = zaxira - ? WHERE id = ?'
        );

        $summa = 0.0;

        foreach ($savat as $mahsulotId => $soni) {
            $olish->execute([$mahsulotId]);
            $mahsulot = $olish->fetch();

            if ($mahsulot === false) {
                throw new RuntimeException("Mahsulot topilmadi: {$mahsulotId}");
            }

            if ((int) $mahsulot['zaxira'] < $soni) {
                throw new RuntimeException(
                    "Omborda yetarli emas: {$mahsulot['nomi']}"
                );
            }

            $qoshish->execute([
                $buyurtmaId, $mahsulotId, $mahsulot['nomi'], $mahsulot['narx'], $soni,
            ]);
            $kamaytirish->execute([$soni, $mahsulotId]);

            $summa += (float) $mahsulot['narx'] * $soni;
        }

        return $summa;
    }

    public function holatniOzgartiring(int $buyurtmaId, string $yangiHolat): void
    {
        $ruxsatEtilgan = [
            'yangi', 'tolangan', 'yigilmoqda', 'yolda', 'yetkazilgan', 'bekor',
        ];

        if (!in_array($yangiHolat, $ruxsatEtilgan, true)) {
            throw new InvalidArgumentException('Noto\'g\'ri holat.');
        }

        $stmt = $this->pdo->prepare('UPDATE buyurtmalar SET holat = ? WHERE id = ?');
        $stmt->execute([$yangiHolat, $buyurtmaId]);
        // Tarix triggerda avtomatik yoziladi
    }
}

-- Bog'liqliklar to'g'ri qurilganmi?
SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'dokon_loyiha'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

-- Indekslar
SELECT TABLE_NAME, INDEX_NAME,
       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS ustunlar
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'dokon_loyiha'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME;

-- Jadval hajmlari
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024, 1) AS hajm_kb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dokon_loyiha'
ORDER BY hajm_kb DESC;

-- So'rov rejasini tekshirish
EXPLAIN SELECT * FROM mahsulotlar WHERE kategoriya_id = 5 AND faol = TRUE;