-- MokoJoomCross 01.00.00 — Initial schema
-- Copyright (C) 2026 Moko Consulting. All rights reserved.
-- SPDX-License-Identifier: GPL-3.0-or-later

CREATE TABLE IF NOT EXISTS `#__mokojoomcross_services` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL DEFAULT '',
    `alias` varchar(400) NOT NULL DEFAULT '',
    `service_type` varchar(50) NOT NULL DEFAULT '' COMMENT 'facebook, twitter, linkedin, mastodon, bluesky, mailchimp, telegram, discord, slack',
    `credentials` text NOT NULL COMMENT 'JSON encrypted — API keys, tokens, secrets',
    `params` text NOT NULL COMMENT 'JSON — service-specific configuration',
    `published` tinyint(1) NOT NULL DEFAULT 0,
    `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',
    `created_by` int(10) unsigned NOT NULL DEFAULT 0,
    `checked_out` int(10) unsigned,
    `checked_out_time` datetime,
    PRIMARY KEY (`id`),
    KEY `idx_published` (`published`),
    KEY `idx_service_type` (`service_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokojoomcross_posts` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `article_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to #__content.id',
    `service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to #__mokojoomcross_services.id',
    `status` varchar(20) NOT NULL DEFAULT 'queued' COMMENT 'queued, posting, posted, failed, scheduled',
    `message` text NOT NULL COMMENT 'Rendered message sent to platform',
    `platform_post_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Post ID returned by platform',
    `platform_response` text NOT NULL COMMENT 'JSON — full API response from platform',
    `scheduled_at` datetime DEFAULT NULL COMMENT 'When to post (NULL = immediately)',
    `posted_at` datetime DEFAULT NULL COMMENT 'When actually posted',
    `retry_count` int(10) unsigned NOT NULL DEFAULT 0,
    `error_message` text NOT NULL,
    `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_article` (`article_id`),
    KEY `idx_service` (`service_id`),
    KEY `idx_status` (`status`),
    KEY `idx_scheduled` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokojoomcross_templates` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `service_type` varchar(50) NOT NULL DEFAULT '' COMMENT 'Platform this template is for (or "default")',
    `title` varchar(255) NOT NULL DEFAULT '',
    `template_body` text NOT NULL COMMENT 'Template with placeholders: {title}, {url}, {introtext}, {image}, {category}, {author}',
    `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_service_type` (`service_type`),
    KEY `idx_published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokojoomcross_logs` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `post_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to #__mokojoomcross_posts.id',
    `service_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to #__mokojoomcross_services.id',
    `level` varchar(20) NOT NULL DEFAULT 'info' COMMENT 'info, warning, error',
    `message` text NOT NULL,
    `context` text NOT NULL COMMENT 'JSON — additional context data',
    `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`),
    KEY `idx_post` (`post_id`),
    KEY `idx_service` (`service_id`),
    KEY `idx_level` (`level`),
    KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default templates
INSERT INTO `#__mokojoomcross_templates` (`service_type`, `title`, `template_body`, `published`, `ordering`, `created`) VALUES
('default', 'Default Template', '{title}\n\n{introtext}\n\n{url}', 1, 1, NOW()),
('twitter', 'Twitter/X Default', '{title}\n\n{url}', 1, 2, NOW()),
('mastodon', 'Mastodon Default', '{title}\n\n{introtext}\n\n{url}\n\n#Joomla', 1, 3, NOW()),
('mailchimp', 'Mailchimp Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 4, NOW()),
('telegram', 'Telegram Default', '<b>{title}</b>\n\n{introtext}\n\n<a href=\"{url}\">Read more</a>', 1, 5, NOW()),
('discord', 'Discord Default', '**{title}**\n\n{introtext}\n\n{url}', 1, 6, NOW()),
('slack', 'Slack Default', '*{title}*\n\n{introtext}\n\n{url}', 1, 7, NOW()),
('facebook', 'Facebook Default', '{title}\n\n{introtext}\n\n{url}', 1, 8, NOW()),
('linkedin', 'LinkedIn Default', '{title}\n\n{introtext}\n\n{url}', 1, 9, NOW()),
('bluesky', 'Bluesky Default', '{title}\n\n{url}', 1, 10, NOW()),
('threads', 'Threads Default', '{title}\n\n{introtext}\n\n{url}', 1, 11, NOW()),
('teams', 'Teams Default', '**{title}**\n\n{introtext}\n\n[Read more]({url})', 1, 12, NOW()),
('medium', 'Medium Default', '{title}\n\n{introtext}\n\n{url}', 1, 13, NOW()),
('wordpress', 'WordPress Default', '{title}\n\n{introtext}\n\n{url}', 1, 14, NOW()),
('webhook', 'Webhook Default', '{title}\n\n{introtext}\n\n{url}', 1, 15, NOW()),
('sendgrid', 'SendGrid Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 16, NOW()),
('brevo', 'Brevo Default', '<h1>{title}</h1>\n<p>{introtext}</p>\n<p><a href=\"{url}\">Read more</a></p>', 1, 17, NOW()),
('ntfy', 'Ntfy Default', '{title}: {introtext}', 1, 18, NOW()),
('reddit', 'Reddit Default', '{title}', 1, 19, NOW()),
('pinterest', 'Pinterest Default', '{title} - {introtext}', 1, 20, NOW());

CREATE TABLE IF NOT EXISTS `#__mokojoomcross_category_rules` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `category_id` int(10) unsigned NOT NULL,
    `service_id` int(10) unsigned NOT NULL,
    `published` tinyint(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_category_service` (`category_id`, `service_id`),
    KEY `idx_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;