-- SQL script to create scheduled_reports table
-- Run this in your database if you cannot run migrations due to PHP version

CREATE TABLE IF NOT EXISTS `scheduled_reports` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_name` varchar(200) NOT NULL,
  `report_type` varchar(50) NOT NULL COMMENT 'attendance, leave, employee, custom',
  `frequency` varchar(20) NOT NULL COMMENT 'daily, weekly, monthly',
  `schedule_time` time NOT NULL COMMENT 'Time to run the report',
  `schedule_day` varchar(20) DEFAULT NULL COMMENT 'Day of week (for weekly) or day of month (for monthly)',
  `filters` text DEFAULT NULL COMMENT 'JSON encoded filters (department_id, location_id, etc.)',
  `format` varchar(10) NOT NULL DEFAULT 'pdf' COMMENT 'pdf, excel, csv',
  `recipients` text NOT NULL COMMENT 'JSON encoded list of email addresses',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  `last_run` datetime DEFAULT NULL,
  `next_run` datetime DEFAULT NULL,
  `created_by` int(11) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `is_active` (`is_active`),
  KEY `next_run` (`next_run`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: To drop the table if you need to recreate it:
-- DROP TABLE IF EXISTS `scheduled_reports`;
