CREATE TABLE IF NOT EXISTS `#__mokojoombackup_profiles` (
	`id`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`title`             VARCHAR(255) NOT NULL DEFAULT '',
	`description`       TEXT NOT NULL,
	`backup_type`       VARCHAR(20) NOT NULL DEFAULT 'full' COMMENT 'full, database, files',
	`archive_format`    VARCHAR(10) NOT NULL DEFAULT 'zip',
	`compression_level` TINYINT(1) UNSIGNED NOT NULL DEFAULT 5 COMMENT '0=none, 9=max',
	`split_size`        INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=no split, otherwise MB per part',
	`backup_dir`        VARCHAR(512) NOT NULL DEFAULT 'administrator/components/com_mokojoombackup/backups',
	`archive_name_format` VARCHAR(512) NOT NULL DEFAULT '[host]_[datetime]_profile[profile_id]' COMMENT 'Filename format with placeholders',
	`exclude_dirs`      TEXT NOT NULL COMMENT 'Newline-separated directory paths to exclude',
	`exclude_files`     TEXT NOT NULL COMMENT 'Newline-separated filename patterns to exclude',
	`exclude_tables`    TEXT NOT NULL COMMENT 'Newline-separated table names to exclude',
	`remote_storage`    VARCHAR(20) NOT NULL DEFAULT 'none' COMMENT 'none, ftp, google_drive, s3',
	`ftp_host`          VARCHAR(255) NOT NULL DEFAULT '',
	`ftp_port`          INT(5) UNSIGNED NOT NULL DEFAULT 21,
	`ftp_username`      VARCHAR(255) NOT NULL DEFAULT '',
	`ftp_password`      VARCHAR(255) NOT NULL DEFAULT '',
	`ftp_path`          VARCHAR(512) NOT NULL DEFAULT '/backups',
	`ftp_passive`       TINYINT(1) NOT NULL DEFAULT 1,
	`ftp_ssl`           TINYINT(1) NOT NULL DEFAULT 0,
	`gdrive_client_id`     VARCHAR(255) NOT NULL DEFAULT '',
	`gdrive_client_secret` VARCHAR(255) NOT NULL DEFAULT '',
	`gdrive_refresh_token` VARCHAR(512) NOT NULL DEFAULT '',
	`gdrive_folder_id`     VARCHAR(255) NOT NULL DEFAULT '',
	`s3_endpoint`          VARCHAR(512) NOT NULL DEFAULT '' COMMENT 'S3 endpoint URL (blank = AWS default)',
	`s3_region`            VARCHAR(50) NOT NULL DEFAULT 'us-east-1',
	`s3_access_key`        VARCHAR(255) NOT NULL DEFAULT '',
	`s3_secret_key`        VARCHAR(255) NOT NULL DEFAULT '',
	`s3_bucket`            VARCHAR(255) NOT NULL DEFAULT '',
	`s3_path`              VARCHAR(512) NOT NULL DEFAULT '/backups',
	`remote_keep_local`    TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Keep local copy after upload',
	`encryption_password`  VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'AES-256 archive encryption password (blank = no encryption)',
	`include_mokorestore`    TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Include MokoRestore standalone restore script in archive',
	`notify_email`         VARCHAR(512) NOT NULL DEFAULT '' COMMENT 'Comma-separated notification emails',
	`notify_user_groups`   VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Comma-separated Joomla user group IDs',
	`notify_on_success`    TINYINT(1) NOT NULL DEFAULT 0,
	`notify_on_failure`    TINYINT(1) NOT NULL DEFAULT 1,
	`published`         TINYINT(1) NOT NULL DEFAULT 1,
	`ordering`          INT(11) NOT NULL DEFAULT 0,
	`created`           DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`modified`          DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`id`),
	KEY `idx_published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokojoombackup_records` (
	`id`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`profile_id`      INT(11) UNSIGNED NOT NULL DEFAULT 1,
	`description`     VARCHAR(255) NOT NULL DEFAULT '',
	`status`          VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, running, complete, fail',
	`origin`          VARCHAR(20) NOT NULL DEFAULT 'backend' COMMENT 'backend, cli, api, scheduled',
	`backup_type`     VARCHAR(20) NOT NULL DEFAULT 'full' COMMENT 'full, database, files',
	`archivename`     VARCHAR(512) NOT NULL DEFAULT '',
	`absolute_path`   VARCHAR(1024) NOT NULL DEFAULT '',
	`total_size`      BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
	`db_size`         BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
	`files_count`     INT(11) UNSIGNED NOT NULL DEFAULT 0,
	`tables_count`    INT(11) UNSIGNED NOT NULL DEFAULT 0,
	`multipart`       INT(11) UNSIGNED NOT NULL DEFAULT 0,
	`tag`             VARCHAR(50) NOT NULL DEFAULT '',
	`backupstart`     DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`backupend`       DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`filesexist`      TINYINT(1) NOT NULL DEFAULT 1,
	`remote_filename` VARCHAR(512) NOT NULL DEFAULT '',
	`checksum`        VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'SHA-256 hash of archive',
	`base_record_id`  INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Base full backup ID for differential',
	`manifest`        LONGTEXT DEFAULT NULL COMMENT 'JSON file manifest for differential comparison',
	`log`             MEDIUMTEXT DEFAULT NULL COMMENT 'Step-by-step backup log',
	PRIMARY KEY (`id`),
	KEY `idx_profile` (`profile_id`),
	KEY `idx_status` (`status`),
	KEY `idx_backupstart` (`backupstart`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default backup profile (IGNORE prevents duplicate key error on update)
INSERT IGNORE INTO `#__mokojoombackup_profiles` (
	`id`, `title`, `description`, `backup_type`,
	`archive_format`, `compression_level`, `split_size`, `backup_dir`,
	`exclude_dirs`, `exclude_files`, `exclude_tables`,
	`published`, `ordering`, `created`, `modified`
) VALUES (
	1, 'Default Backup Profile', 'Full site backup with default settings', 'full',
	'zip', 5, 0, 'administrator/components/com_mokojoombackup/backups',
	'administrator/components/com_mokojoombackup/backups\ntmp\ncache\nlogs\nadministrator/logs',
	'.gitignore\n.htaccess.bak',
	'#__session',
	1, 1, NOW(), NOW()
);