-- OVOZ BERISH TIZIMI — baza sxemasi
-- cPanel > phpMyAdmin > Import orqali ishga tushiring

CREATE TABLE IF NOT EXISTS polls (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,          -- masalan: "Qaysi mahalla zo'r?"
    description     TEXT NULL,
    status          ENUM('draft','active','finished') NOT NULL DEFAULT 'draft',
    deadline        DATETIME NOT NULL,              -- masalan: 2026-08-30 23:59:00
    channel_msg_id  INT NULL,                       -- kanaldagi e'lon post ID (yangilab turish uchun)
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1-daraja: tumanlar / shaharlar (ichma-ich tugmalarning birinchi qatlami)
CREATE TABLE IF NOT EXISTS categories (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    poll_id    INT NOT NULL,
    name       VARCHAR(255) NOT NULL,               -- masalan: "Guliston shahri"
    sort       INT NOT NULL DEFAULT 0,
    FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2-daraja: nomzodlar — mahallalar / maktablar (ovoz shularga beriladi)
CREATE TABLE IF NOT EXISTS options (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    poll_id      INT NOT NULL,
    category_id  INT NOT NULL,
    name         VARCHAR(255) NOT NULL,             -- masalan: "Do'stlik mahallasi"
    votes_count  INT NOT NULL DEFAULT 0,            -- tez ko'rsatish uchun hisoblagich
    sort         INT NOT NULL DEFAULT 0,
    FOREIGN KEY (poll_id)     REFERENCES polls(id)      ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ovozlar: bitta poll ichida bitta user faqat BIR marta
CREATE TABLE IF NOT EXISTS votes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    poll_id     INT NOT NULL,
    option_id   INT NOT NULL,
    user_id     BIGINT NOT NULL,                    -- Telegram user ID
    first_name  VARCHAR(255) NULL,
    username    VARCHAR(255) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_vote (poll_id, user_id),        -- << 1 akkaunt = 1 ovoz kafolati
    FOREIGN KEY (poll_id)   REFERENCES polls(id)   ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES options(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;