-- Package 01B
-- Indexes, constraints, and report-safe uniqueness aligned to current schema.

SET @db := DATABASE();

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='users' AND index_name='uq_users_company_staff_code'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD UNIQUE KEY uq_users_company_staff_code (company_id, staff_code)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='users' AND index_name='idx_users_company'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD KEY idx_users_company (company_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='worksites' AND index_name='idx_worksites_company'
    ),
    'SELECT 1',
    'ALTER TABLE worksites ADD KEY idx_worksites_company (company_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='user_worksites' AND index_name='idx_user_worksites_company'
    ),
    'SELECT 1',
    'ALTER TABLE user_worksites ADD KEY idx_user_worksites_company (company_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='work_sessions' AND index_name='idx_work_sessions_company_date'
    ),
    'SELECT 1',
    'ALTER TABLE work_sessions ADD KEY idx_work_sessions_company_date (company_id, session_date)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='work_sessions' AND index_name='idx_work_sessions_company_user_date'
    ),
    'SELECT 1',
    'ALTER TABLE work_sessions ADD KEY idx_work_sessions_company_user_date (company_id, user_id, session_date)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='work_sessions' AND index_name='idx_work_sessions_company_status'
    ),
    'SELECT 1',
    'ALTER TABLE work_sessions ADD KEY idx_work_sessions_company_status (company_id, session_status)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='work_session_events' AND index_name='idx_wse_company_session'
    ),
    'SELECT 1',
    'ALTER TABLE work_session_events ADD KEY idx_wse_company_session (company_id, work_session_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema=@db AND table_name='work_session_events' AND index_name='idx_wse_company_user_time'
    ),
    'SELECT 1',
    'ALTER TABLE work_session_events ADD KEY idx_wse_company_user_time (company_id, user_id, event_time)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE users MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE users MODIFY staff_code VARCHAR(50) NOT NULL;
ALTER TABLE users MODIFY full_name VARCHAR(150) NOT NULL;

ALTER TABLE worksites MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE user_worksites MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE work_sessions MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE work_session_events MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE company_holidays MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE payroll_rule_sets MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE payroll_rule_set_days MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE staff_payroll_rules MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE location_logs MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE location_stops MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE api_sessions MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE admin_audit_log MODIFY company_id BIGINT(20) UNSIGNED NOT NULL;