-- Admins table for multi-level admin system
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('SUPER_ADMIN','STATE_ADMIN','DISTRICT_ADMIN','BLOCK_ADMIN','PANCHAYAT_ADMIN') NOT NULL,
    state_id INT DEFAULT NULL,
    district_id INT DEFAULT NULL,
    block_id INT DEFAULT NULL,
    panchayat_id INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (state_id) REFERENCES states(id),
    FOREIGN KEY (district_id) REFERENCES districts(id),
    FOREIGN KEY (block_id) REFERENCES blocks(id),
    FOREIGN KEY (panchayat_id) REFERENCES gps(id)
);

-- Indexes for faster lookups
CREATE INDEX idx_admins_role ON admins(role);
CREATE INDEX idx_admins_state ON admins(state_id);
CREATE INDEX idx_admins_district ON admins(district_id);
CREATE INDEX idx_admins_block ON admins(block_id);
CREATE INDEX idx_admins_panchayat ON admins(panchayat_id);
