-- Backup System Schema
-- Glado POS System

-- Table for storing backup records
CREATE TABLE IF NOT EXISTS `backups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` bigint(20) DEFAULT 0,
  `google_drive_file_id` varchar(255) DEFAULT NULL,
  `backup_type` enum('automatic','manual') DEFAULT 'automatic',
  `status` enum('pending','completed','failed','deleted') DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_branch_id` (`branch_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_backups_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_backups_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for backup settings per branch
CREATE TABLE IF NOT EXISTS `backup_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL,
  `auto_backup_enabled` tinyint(1) DEFAULT 1,
  `backup_time` time DEFAULT '23:59:00',
  `retention_days` int(11) DEFAULT 3,
  `google_drive_sync` tinyint(1) DEFAULT 1,
  `notification_emails` text DEFAULT NULL,
  `compress_backups` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_backup_settings_branch` (`branch_id`),
  CONSTRAINT `fk_backup_settings_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default backup settings for existing branches
INSERT IGNORE INTO `backup_settings` (`branch_id`, `auto_backup_enabled`, `retention_days`, `google_drive_sync`, `compress_backups`)
SELECT `id`, 1, 3, 1, 1 FROM `branches`;

-- Table for backup logs (detailed logging)
CREATE TABLE IF NOT EXISTS `backup_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `backup_id` int(11) NOT NULL,
  `log_level` enum('info','warning','error','debug') DEFAULT 'info',
  `message` text NOT NULL,
  `context` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_backup_id` (`backup_id`),
  KEY `idx_log_level` (`log_level`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_backup_logs_backup` FOREIGN KEY (`backup_id`) REFERENCES `backups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_backups_branch_date` ON `backups` (`branch_id`, `created_at`);
CREATE INDEX IF NOT EXISTS `idx_backups_filename` ON `backups` (`filename`);
CREATE INDEX IF NOT EXISTS `idx_backup_logs_level_date` ON `backup_logs` (`log_level`, `created_at`);