-- ====================================================================
-- Authors module (2026-09-23)
--   1. `authors` table — converted from the PostgreSQL definition:
--        id TEXT PRIMARY KEY          → VARCHAR(36) DEFAULT (UUID())
--        TIMESTAMPTZ DEFAULT NOW()    → TIMESTAMP DEFAULT CURRENT_TIMESTAMP
--                                       (+ ON UPDATE for updated_at)
--        BOOLEAN                      → TINYINT(1)
--      plus `deleted_at` for soft delete, same as every other admin module.
--   2. `posts.author_id` (nullable FK, ON DELETE SET NULL).
--   3. `authors:*` permissions, granted to the super_admin role.
-- Safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `authors` (
  `id`               VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
  `full_name`        VARCHAR(100) NOT NULL,
  `slug`             VARCHAR(255) NOT NULL,
  `bio`              TEXT         DEFAULT NULL,
  `profile_image`    VARCHAR(500) DEFAULT NULL COMMENT 'Cloudinary public_id',
  `website`          VARCHAR(500) DEFAULT NULL,
  `social_facebook`  VARCHAR(500) DEFAULT NULL,
  `social_twitter`   VARCHAR(500) DEFAULT NULL,
  `social_instagram` VARCHAR(500) DEFAULT NULL,
  `social_linkedin`  VARCHAR(500) DEFAULT NULL,
  `status`           TINYINT(1)   NOT NULL DEFAULT 1,
  `deleted_at`       TIMESTAMP    NULL DEFAULT NULL,
  `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `uk_authors_slug` (`slug`),
  INDEX `idx_authors_status` (`status`),
  INDEX `idx_authors_deleted` (`deleted_at`),
  INDEX `idx_authors_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- posts.author_id — MySQL has no ADD COLUMN IF NOT EXISTS, so guard via
-- information_schema.
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'posts' AND COLUMN_NAME = 'author_id');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `posts`
     ADD COLUMN `author_id` VARCHAR(36) NULL DEFAULT NULL AFTER `category_id`,
     ADD INDEX `idx_posts_author` (`author_id`),
     ADD CONSTRAINT `fk_posts_author` FOREIGN KEY (`author_id`) REFERENCES `authors`(`id`) ON DELETE SET NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Permissions — same name/action conventions as product_tags / posts.
INSERT IGNORE INTO `permissions` (`id`, `name`, `module`, `action`) VALUES
  (UUID(), 'authors:read',                'authors', 'read'),
  (UUID(), 'authors:create',              'authors', 'create'),
  (UUID(), 'authors:update',              'authors', 'update'),
  (UUID(), 'authors:delete',              'authors', 'delete'),
  (UUID(), 'authors:bulk-delete',         'authors', 'bulkdelete'),
  (UUID(), 'authors:activate-deactivate', 'authors', 'active_deactive'),
  (UUID(), 'authors:view-deleted',        'authors', 'view_deleted'),
  (UUID(), 'authors:permanent-delete',    'authors', 'delete_permanent'),
  (UUID(), 'authors:restore',             'authors', 'restore'),
  (UUID(), 'authors:bulk-restore',        'authors', 'restore_permanent'),
  (UUID(), 'authors:view-timeline',       'authors', 'view_timeline');

INSERT IGNORE INTO `role_permissions` (`id`, `role_id`, `permission_id`)
SELECT UUID(), r.id, p.id
FROM `roles` r
JOIN `permissions` p ON p.module = 'authors'
WHERE r.name = 'super_admin';
