-- =====================================================
-- UPDIT DATABASE SCHEMA
-- Manual SQL Import for cPanel/phpMyAdmin
-- Run this if you cannot use the web installer
-- =====================================================


-- ----------------------------
-- Table: plans
-- ----------------------------
CREATE TABLE `plans` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `max_videos_per_batch` int NOT NULL DEFAULT 5,
  `min_interval_minutes` int NOT NULL DEFAULT 10,
  `daily_upload_cap` int NOT NULL DEFAULT 10,
  `price_id` varchar(255) DEFAULT NULL,
  `price` decimal(8,2) NOT NULL DEFAULT 0.00,
  `features` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Data: plans
-- ----------------------------
INSERT INTO `plans` VALUES 
(1, 'Free', 5, 30, 10, NULL, 0.00, '[\"5 videos per batch\",\"30 min minimum interval\",\"10 uploads per day\",\"1 connected account\"]', NOW(), NOW()),
(2, 'Pro', 15, 10, 50, 'price_pro_monthly', 19.99, '[\"15 videos per batch\",\"10 min minimum interval\",\"50 uploads per day\",\"3 connected accounts\",\"Priority support\"]', NOW(), NOW()),
(3, 'Agency', 50, 5, 200, 'price_agency_monthly', 49.99, '[\"50 videos per batch\",\"5 min minimum interval\",\"200 uploads per day\",\"Unlimited connected accounts\",\"Priority support\",\"API access\"]', NOW(), NOW());

-- ----------------------------
-- Table: schedules
-- ----------------------------
CREATE TABLE `schedules` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `social_account_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `upload_mode` enum('normal','shorts') NOT NULL DEFAULT 'normal',
  `interval_minutes` int NOT NULL DEFAULT 10,
  `auto_restart` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','paused','completed','quota_limit') NOT NULL DEFAULT 'active',
  `daily_uploaded_count` int NOT NULL DEFAULT 0,
  `next_run_at` timestamp NULL DEFAULT NULL,
  `resume_at` timestamp NULL DEFAULT NULL,
  `last_reset_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `schedules_status_next_run_at_index` (`status`,`next_run_at`),
  KEY `schedules_status_resume_at_index` (`status`,`resume_at`),
  CONSTRAINT `schedules_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `schedules_social_account_id_foreign` FOREIGN KEY (`social_account_id`) REFERENCES `social_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: videos
-- ----------------------------
CREATE TABLE `videos` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `schedule_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `original_filename` varchar(255) NOT NULL,
  `file_size` bigint NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL,
  `description` text,
  `tags` varchar(255) DEFAULT NULL,
  `is_uploaded` tinyint(1) NOT NULL DEFAULT 0,
  `upload_attempts` int NOT NULL DEFAULT 0,
  `platform_video_id` varchar(255) DEFAULT NULL,
  `upload_error` text,
  `uploaded_at` timestamp NULL DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `videos_schedule_uploaded_index` (`schedule_id`,`is_uploaded`,`sort_order`),
  CONSTRAINT `videos_schedule_id_foreign` FOREIGN KEY (`schedule_id`) REFERENCES `schedules` (`id`) ON DELETE CASCADE,
  CONSTRAINT `videos_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: upload_logs
-- ----------------------------
CREATE TABLE `upload_logs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `schedule_id` bigint UNSIGNED DEFAULT NULL,
  `video_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('success','failed','quota_exceeded','auth_error') NOT NULL,
  `message` text,
  `details` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `upload_logs_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `upload_logs_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: sessions
-- ----------------------------
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: jobs (for queue)
-- ----------------------------
CREATE TABLE `jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint UNSIGNED NOT NULL,
  `reserved_at` int UNSIGNED DEFAULT NULL,
  `available_at` int UNSIGNED NOT NULL,
  `created_at` int UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- INSTALLATION COMPLETE!
-- 
-- Default Admin Login:
-- Email: admin@updit.com
-- Password: admin123
--
-- After importing, create file: storage/installed
-- =====================================================
