-- ====================================================
--  দুর্নীতি Admin Panel — Full Database Schema
--  MySQL 5.7+ / MariaDB 10.3+
--  Roles: super_admin, district_admin, citizen
-- ====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Users ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(150) NOT NULL,
    mobile       VARCHAR(30)  NOT NULL UNIQUE,
    email        VARCHAR(190) NULL UNIQUE,
    nid          VARCHAR(30)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    division     VARCHAR(100) NOT NULL,
    district     VARCHAR(100) NOT NULL,
    upazila      VARCHAR(100) NOT NULL,
    village      VARCHAR(120) NOT NULL,
    -- Roles: citizen | district_admin | super_admin
    role         VARCHAR(30)  NOT NULL DEFAULT 'citizen',
    is_verified  TINYINT(1)   NOT NULL DEFAULT 1,
    created_at   TIMESTAMP    NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP    NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_district (district)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Categories ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug       VARCHAR(80)  NOT NULL UNIQUE,
    name       VARCHAR(150) NOT NULL,
    icon       VARCHAR(80)  NOT NULL,
    sort_order INT          NOT NULL DEFAULT 0,
    created_at TIMESTAMP    NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Reports ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reports (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NULL,
    category_id  BIGINT UNSIGNED NOT NULL,
    title        VARCHAR(190) NOT NULL,
    description  TEXT NOT NULL,
    division     VARCHAR(100) NULL,
    district     VARCHAR(100) NULL,
    upazila      VARCHAR(100) NULL,
    village      VARCHAR(120) NULL,
    is_anonymous TINYINT(1)   NOT NULL DEFAULT 1,
    -- status: pending | reviewing | investigating | resolved | rejected
    status       VARCHAR(40)  NOT NULL DEFAULT 'pending',
    admin_note   TEXT NULL COMMENT 'Admin internal note',
    assigned_to  BIGINT UNSIGNED NULL COMMENT 'Assigned admin user id',
    created_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_reports_user (user_id),
    INDEX idx_reports_status (status),
    INDEX idx_reports_district (district),
    INDEX idx_reports_category (category_id),
    CONSTRAINT fk_reports_user     FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_reports_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Report Attachments ─────────────────────────────
CREATE TABLE IF NOT EXISTS report_attachments (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_id     BIGINT UNSIGNED NOT NULL,
    file_path     VARCHAR(255) NOT NULL,
    original_name VARCHAR(190) NULL,
    mime_type     VARCHAR(120) NULL,
    size_bytes    BIGINT UNSIGNED NULL,
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_report_attachments_report (report_id),
    CONSTRAINT fk_report_attachments_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Notifications ──────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NULL,
    title      VARCHAR(190) NOT NULL,
    body       TEXT NOT NULL,
    type       VARCHAR(40) NOT NULL DEFAULT 'info' COMMENT 'info|warning|success|danger',
    is_read    TINYINT(1)   NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notifications_user (user_id),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Admin Activity Log ─────────────────────────────
CREATE TABLE IF NOT EXISTS admin_activity_log (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id   BIGINT UNSIGNED NOT NULL,
    action     VARCHAR(80)  NOT NULL,
    target     VARCHAR(80)  NULL COMMENT 'e.g. report, user',
    target_id  BIGINT UNSIGNED NULL,
    details    TEXT NULL,
    ip_address VARCHAR(45)  NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_log_admin (admin_id),
    INDEX idx_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ====================================================
--  Default Data
-- ====================================================

-- Categories
INSERT INTO categories (slug, name, icon, sort_order) VALUES
('bribe',     'ঘুষ / দুর্নীতি',     'handshake',       1),
('drug',      'মাদক',               'local_pharmacy',   2),
('theft',     'চুরি / ডাকাতি',      'person',           3),
('govt',      'সরকারি অনিয়ম',       'account_balance',  4),
('extortion', 'চাঁদাবাজি / সন্ত্রাস', 'local_police',    5),
('women',     'নারী নির্যাতন',       'support_agent',    6),
('cyber',     'সাইবার অপরাধ',       'computer',         7),
('other',     'অন্যান্য',            'more_horiz',       8)
ON DUPLICATE KEY UPDATE name = VALUES(name), icon = VALUES(icon), sort_order = VALUES(sort_order);

-- Super Admin (password: password)
INSERT INTO users (name, mobile, email, nid, password_hash, division, district, upazila, village, role, is_verified, created_at, updated_at)
VALUES (
    'সুপার অ্যাডমিন',
    '01700000000',
    'superadmin@durniti.local',
    '0000000000001',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.',
    'ঢাকা', 'ঢাকা', 'ঢাকা সদর', 'প্রশাসন',
    'super_admin', 1, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE role = 'super_admin', is_verified = 1;

-- District Admin — Dhaka (password: password)
INSERT INTO users (name, mobile, email, nid, password_hash, division, district, upazila, village, role, is_verified, created_at, updated_at)
VALUES (
    'ঢাকা জেলা অ্যাডমিন',
    '01800000001',
    'dhaka.admin@durniti.local',
    '0000000000002',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.',
    'ঢাকা', 'ঢাকা', 'ঢাকা সদর', 'প্রশাসন',
    'district_admin', 1, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE role = 'district_admin', is_verified = 1;

-- District Admin — Chittagong (password: password)
INSERT INTO users (name, mobile, email, nid, password_hash, division, district, upazila, village, role, is_verified, created_at, updated_at)
VALUES (
    'চট্টগ্রাম জেলা অ্যাডমিন',
    '01800000002',
    'ctg.admin@durniti.local',
    '0000000000003',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.',
    'চট্টগ্রাম', 'চট্টগ্রাম', 'ডবলমুরিং', 'প্রশাসন',
    'district_admin', 1, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE role = 'district_admin', is_verified = 1;

-- Demo Reports
INSERT INTO notifications (user_id, title, body, type, is_read, created_at)
SELECT NULL, 'সিস্টেম চালু হয়েছে', 'দুর্নীতি রিপোর্টিং সিস্টেম সফলভাবে চালু হয়েছে।', 'success', 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM notifications WHERE title = 'সিস্টেম চালু হয়েছে' LIMIT 1);