-- Package 01B
-- DB-aligned tenant foundation migration for current uent_Attendance schema
-- Safe-first version: creates tenant tables and adds nullable company fields aligned to CURRENT dump.

CREATE TABLE IF NOT EXISTS companies (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    company_name VARCHAR(150) NOT NULL,
    trading_name VARCHAR(150) DEFAULT NULL,
    slug VARCHAR(120) NOT NULL,
    logo_path VARCHAR(255) DEFAULT NULL,
    primary_color VARCHAR(20) DEFAULT NULL,
    secondary_color VARCHAR(20) DEFAULT NULL,
    contact_email VARCHAR(150) DEFAULT NULL,
    contact_phone VARCHAR(50) DEFAULT NULL,
    address_line_1 VARCHAR(150) DEFAULT NULL,
    address_line_2 VARCHAR(150) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    region VARCHAR(100) DEFAULT NULL,
    postal_code VARCHAR(30) DEFAULT NULL,
    country_code CHAR(2) NOT NULL DEFAULT 'ZA',
    timezone VARCHAR(64) NOT NULL DEFAULT 'Africa/Johannesburg',
    status ENUM('ACTIVE','INACTIVE','SUSPENDED') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_companies_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_settings (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    company_id BIGINT(20) UNSIGNED NOT NULL,
    default_radius_meters INT(10) UNSIGNED NOT NULL DEFAULT 150,
    clock_in_geofence_mode ENUM('NONE','SOFT','HARD') NOT NULL DEFAULT 'SOFT',
    clock_out_geofence_mode ENUM('NONE','SOFT','HARD') NOT NULL DEFAULT 'SOFT',
    track_only_while_clocked_in TINYINT(1) NOT NULL DEFAULT 1,
    allow_supervisor_override TINYINT(1) NOT NULL DEFAULT 0,
    require_override_reason TINYINT(1) NOT NULL DEFAULT 0,
    policy_scope_mode ENUM('COMPANY','STAFF') NOT NULL DEFAULT 'COMPANY',
    allow_multiple_sessions_per_day TINYINT(1) NOT NULL DEFAULT 1,
    require_primary_active_worksite TINYINT(1) NOT NULL DEFAULT 1,
    enable_midnight_auto_close TINYINT(1) NOT NULL DEFAULT 1,
    auto_close_time TIME NOT NULL DEFAULT '23:59:59',
    auto_close_reason_text VARCHAR(255) NOT NULL DEFAULT 'Auto-closed at midnight',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_company_settings_company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS staff_attendance_policies (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    company_id BIGINT(20) UNSIGNED NOT NULL,
    user_id BIGINT(20) UNSIGNED NOT NULL,
    clock_in_geofence_mode ENUM('NONE','SOFT','HARD') DEFAULT NULL,
    clock_out_geofence_mode ENUM('NONE','SOFT','HARD') DEFAULT NULL,
    radius_meters INT(10) UNSIGNED DEFAULT NULL,
    allow_supervisor_override TINYINT(1) DEFAULT NULL,
    track_route_while_clocked_in TINYINT(1) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    notes VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_staff_attendance_policy_user (user_id),
    KEY idx_staff_attendance_company_user (company_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS staff_code VARCHAR(50) NULL AFTER company_id,
    ADD COLUMN IF NOT EXISTS full_name VARCHAR(150) NULL AFTER staff_code;

ALTER TABLE worksites
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE user_worksites
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE work_sessions
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS auto_closed_flag TINYINT(1) NOT NULL DEFAULT 0 AFTER close_reason,
    ADD COLUMN IF NOT EXISTS needs_review TINYINT(1) NOT NULL DEFAULT 0 AFTER auto_closed_flag,
    ADD COLUMN IF NOT EXISTS worked_minutes INT NOT NULL DEFAULT 0 AFTER needs_review,
    ADD COLUMN IF NOT EXISTS lunch_minutes INT NOT NULL DEFAULT 0 AFTER worked_minutes;

ALTER TABLE work_session_events
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS event_status VARCHAR(50) NULL AFTER event_type,
    ADD COLUMN IF NOT EXISTS event_reason VARCHAR(255) NULL AFTER event_status,
    ADD COLUMN IF NOT EXISTS distance_meters DECIMAL(10,2) NULL AFTER accuracy_meters;

ALTER TABLE company_holidays
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE payroll_rule_sets
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE payroll_rule_set_days
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE staff_payroll_rules
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE location_logs
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE location_stops
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;

ALTER TABLE api_sessions
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER user_id;

ALTER TABLE admin_audit_log
    ADD COLUMN IF NOT EXISTS company_id BIGINT(20) UNSIGNED NULL AFTER id;