Amaliy loyiha - kitob do'koni tahlili

CREATE TABLE muallif (
    id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism text NOT NULL UNIQUE
);

CREATE TABLE kitob (
    id         int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn       text NOT NULL UNIQUE,
    nom        text NOT NULL,
    muallif_id int  NOT NULL REFERENCES muallif(id),
    narx       numeric(10,2) NOT NULL CHECK (narx > 0),
    teglar     text[] NOT NULL DEFAULT '{}',
    xossa      jsonb  NOT NULL DEFAULT '{}'
);

CREATE TABLE mijoz (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism    text NOT NULL,
    shahar text NOT NULL
);

CREATE TABLE buyurtma (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mijoz_id int  NOT NULL REFERENCES mijoz(id),
    sana     date NOT NULL
);

CREATE TABLE buyurtma_qator (
    buyurtma_id int NOT NULL REFERENCES buyurtma(id) ON DELETE CASCADE,
    kitob_id    int NOT NULL REFERENCES kitob(id),
    soni        int NOT NULL CHECK (soni > 0),
    narx        numeric(10,2) NOT NULL CHECK (narx > 0),
    PRIMARY KEY (buyurtma_id, kitob_id)
);

INSERT INTO muallif (ism) VALUES
    ('Abdulla Qodiriy'), ('Cho''lpon'), ('Oybek'), ('Said Ahmad');

INSERT INTO kitob (isbn, nom, muallif_id, narx, teglar, xossa) VALUES
    ('978-1', 'Otkan kunlar',   1,  85000, '{roman,tarixiy}',
     '{"yil":1926,"sahifa":420}'),
    ('978-2', 'Mehrobdan chayon',1, 72000, '{roman,tarixiy}',
     '{"yil":1929,"sahifa":380}'),
    ('978-3', 'Kecha va kunduz', 2, 68000, '{roman}',
     '{"yil":1936,"sahifa":310}'),
    ('978-4', 'Navoiy',          3, 95000, '{roman,tarixiy}',
     '{"yil":1944,"sahifa":520}'),
    ('978-5', 'Ufq',             4, 64000, '{roman,zamonaviy}',
     '{"yil":1964,"sahifa":290}');

INSERT INTO mijoz (ism, shahar) VALUES
    ('Husanboy', 'Namangan'),
    ('Malika',   'Namangan'),
    ('Nodira',   'Toshkent'),
    ('Aziza',    'Samarqand'),
    ('Kamola',   'Namangan');

INSERT INTO buyurtma (mijoz_id, sana) VALUES
    (1, '2026-01-12'), (2, '2026-01-18'), (1, '2026-02-03'),
    (3, '2026-02-14'), (4, '2026-02-27'), (5, '2026-03-05'),
    (2, '2026-03-11'), (1, '2026-03-22');

INSERT INTO buyurtma_qator (buyurtma_id, kitob_id, soni, narx) VALUES
    (1, 1, 1, 85000), (1, 3, 2, 68000),
    (2, 4, 1, 95000),
    (3, 2, 1, 72000), (3, 5, 1, 64000),
    (4, 1, 3, 85000),
    (5, 4, 1, 95000), (5, 2, 2, 72000),
    (6, 5, 1, 64000),
    (7, 1, 1, 85000), (7, 4, 1, 95000),
    (8, 3, 1, 68000);

SELECT count(DISTINCT b.id)                 AS buyurtmalar,
       count(DISTINCT b.mijoz_id)           AS mijozlar,
       sum(q.soni)                          AS sotilgan_nusxa,
       sum(q.soni * q.narx)                 AS tushum,
       round(avg(q.soni * q.narx), 0)       AS ortacha_qator
FROM buyurtma b
JOIN buyurtma_qator q ON q.buyurtma_id = b.id;

SELECT k.nom,
       m.ism                AS muallif,
       sum(q.soni)          AS nusxa,
       sum(q.soni * q.narx) AS tushum
FROM buyurtma_qator q
JOIN kitob k   ON k.id = q.kitob_id
JOIN muallif m ON m.id = k.muallif_id
GROUP BY k.nom, m.ism
ORDER BY nusxa DESC, k.nom;

WITH oylik AS (
    SELECT date_trunc('month', b.sana)::date AS oy,
           sum(q.soni * q.narx)              AS tushum
    FROM buyurtma b
    JOIN buyurtma_qator q ON q.buyurtma_id = b.id
    GROUP BY 1
)
SELECT oy,
       tushum,
       lag(tushum) OVER (ORDER BY oy)                AS otgan_oy,
       tushum - lag(tushum) OVER (ORDER BY oy)       AS farq,
       sum(tushum) OVER (ORDER BY oy)                AS jamlangan
FROM oylik
ORDER BY oy;

WITH mijoz_summa AS (
    SELECT m.shahar,
           m.ism,
           sum(q.soni * q.narx) AS summa
    FROM mijoz m
    JOIN buyurtma b       ON b.mijoz_id = m.id
    JOIN buyurtma_qator q ON q.buyurtma_id = b.id
    GROUP BY m.shahar, m.ism
),
reyting AS (
    SELECT shahar, ism, summa,
           row_number() OVER (PARTITION BY shahar
                              ORDER BY summa DESC) AS orin
    FROM mijoz_summa
)
SELECT shahar, ism, summa
FROM reyting
WHERE orin = 1
ORDER BY summa DESC;

SELECT k.nom,
       k.teglar,
       (k.xossa->>'yil')::int     AS yil,
       (k.xossa->>'sahifa')::int  AS sahifa
FROM kitob k
WHERE k.teglar @> ARRAY['tarixiy']
  AND (k.xossa->>'sahifa')::int > 350
ORDER BY yil;

SELECT t.teg, count(*) AS kitoblar
FROM kitob k, unnest(k.teglar) AS t(teg)
GROUP BY t.teg
ORDER BY kitoblar DESC, t.teg;

INSERT INTO kitob (isbn, nom, muallif_id, narx, teglar, xossa)
VALUES ('978-1', 'Otkan kunlar', 1, 92000,
        '{roman,tarixiy,klassika}', '{"yil":1926,"sahifa":420}')
ON CONFLICT (isbn) DO UPDATE
    SET narx   = EXCLUDED.narx,
        teglar = EXCLUDED.teglar
RETURNING isbn, nom, narx, teglar;

CREATE TABLE narx_tarixi (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kitob_id int NOT NULL,
    eski     numeric(10,2) NOT NULL,
    yangi    numeric(10,2) NOT NULL
);

CREATE FUNCTION narxni_yoz() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.narx IS DISTINCT FROM OLD.narx THEN
        INSERT INTO narx_tarixi (kitob_id, eski, yangi)
        VALUES (OLD.id, OLD.narx, NEW.narx);
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER kitob_narx_audit
    AFTER UPDATE ON kitob
    FOR EACH ROW EXECUTE FUNCTION narxni_yoz();

UPDATE kitob SET narx = narx * 1.10 WHERE teglar @> ARRAY['tarixiy'];

SELECT kitob_id, eski, yangi FROM narx_tarixi ORDER BY kitob_id;

CREATE INDEX buyurtma_sana_idx ON buyurtma (sana);
CREATE INDEX qator_kitob_idx   ON buyurtma_qator (kitob_id);
CREATE INDEX kitob_teglar_idx  ON kitob USING gin (teglar);
CREATE INDEX kitob_xossa_idx   ON kitob USING gin (xossa);

SELECT indexname
FROM pg_indexes
WHERE tablename IN ('kitob', 'buyurtma', 'buyurtma_qator')
ORDER BY indexname;

CREATE VIEW kitob_sotuvi AS
SELECT k.id,
       k.nom,
       m.ism                              AS muallif,
       coalesce(sum(q.soni), 0)           AS nusxa,
       coalesce(sum(q.soni * q.narx), 0)  AS tushum
FROM kitob k
JOIN muallif m           ON m.id = k.muallif_id
LEFT JOIN buyurtma_qator q ON q.kitob_id = k.id
GROUP BY k.id, k.nom, m.ism;

SELECT nom, muallif, nusxa, tushum
FROM kitob_sotuvi
WHERE nusxa > 2
ORDER BY tushum DESC;

BEGIN;

INSERT INTO buyurtma (mijoz_id, sana)
VALUES (5, '2026-03-28')
RETURNING id AS yangi_buyurtma;

INSERT INTO buyurtma_qator (buyurtma_id, kitob_id, soni, narx)
SELECT currval(pg_get_serial_sequence('buyurtma', 'id')),
       k.id, 2, k.narx
FROM kitob k WHERE k.isbn = '978-3';

COMMIT;

SELECT b.id, m.ism, b.sana, q.soni, q.narx
FROM buyurtma b
JOIN mijoz m          ON m.id = b.mijoz_id
JOIN buyurtma_qator q ON q.buyurtma_id = b.id
WHERE b.sana = '2026-03-28';