

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;
