-- Create database
CREATE DATABASE IF NOT EXISTS nomination_system;
USE nomination_system;

-- Admin table
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_approved BOOLEAN DEFAULT 0,
    
    -- NEW: Admin controls these separately
    available_for_nomination BOOLEAN DEFAULT 0,  -- Admin sets: Can this person BE nominated?
    can_nominate BOOLEAN DEFAULT 1,              -- Admin sets: Can this person NOMINATE others?
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE members ADD COLUMN password_reset_token VARCHAR(64) NULL;
ALTER TABLE members ADD COLUMN password_reset_expires DATETIME NULL;
ALTER TABLE members ADD COLUMN last_password_change DATETIME NULL;

-- Positions table
CREATE TABLE positions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50) UNIQUE NOT NULL
);

-- Insert the 7 positions
INSERT INTO positions (title) VALUES 
('Chairperson'),
('Treasurer'),
('General Secretary'),
('Logistics Coordinator'),
('Captain'),
('Asst. Captain'),
('Events Coordinator');

-- Nominations table
CREATE TABLE nominations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nominator_id INT NOT NULL,
    nominee_id INT NOT NULL,
    position_id INT NOT NULL,
    nominated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (nominator_id) REFERENCES members(id) ON DELETE CASCADE,
    FOREIGN KEY (nominee_id) REFERENCES members(id) ON DELETE CASCADE,
    FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_nomination (nominee_id, position_id),
    INDEX idx_nominee (nominee_id),
    INDEX idx_nominator (nominator_id)
);