-- DOJ License Manager — Full Schema (v3 - file-based images)
-- InfinityFree: Create DB in cPanel first, then import this.

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    role ENUM('admin','staff') DEFAULT 'staff',
    rank_title ENUM('clerk','magistrate','judge','justice','ass_chief_justice','chief_justice') DEFAULT 'clerk',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS businesses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    owner_name VARCHAR(100) NOT NULL,
    address VARCHAR(255),
    city VARCHAR(100),
    phone VARCHAR(30),
    business_type VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    permit_number VARCHAR(50) UNIQUE NOT NULL,
    permit_type ENUM('food_truck','food','liquor') NOT NULL,
    issued_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    status ENUM('active','expired','revoked','suspended') DEFAULT 'active',
    issued_by INT,
    signed_by VARCHAR(120) NULL,
    signed_rank VARCHAR(100) NULL,
    notes TEXT,
    image_filename VARCHAR(150) NOT NULL,
    image_last_rendered TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inspections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    inspection_type ENUM('food_safety','cleanliness','workplace_safety') NOT NULL DEFAULT 'food_safety',
    inspection_number VARCHAR(50) UNIQUE NOT NULL,
    grade ENUM('A','B','C','D','F') NOT NULL,
    score INT NOT NULL,
    inspection_date DATE NOT NULL,
    inspector_name VARCHAR(100),
    signed_by VARCHAR(120) NULL,
    signed_rank VARCHAR(100) NULL,
    notes TEXT,
    violations TEXT,
    status ENUM('active','superseded') DEFAULT 'active',
    image_filename VARCHAR(150) NOT NULL,
    image_last_rendered TIMESTAMP NULL,
    issued_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100),
    entity_type VARCHAR(50),
    entity_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin (password: Admin1234!)
INSERT IGNORE INTO users (username, password_hash, full_name, email, role, rank_title)
VALUES ('admin','$2y$12$LzBKg2JGJhUzuKwHgRNHzuKWHn2HMdZLpEz0SFJKvRbGb3rUiIbHO','System Administrator','admin@doj.local','admin','chief_justice');