-- =====================================================================
-- MIGRATION 003  (Bloco 3 — Mídia do Gabinete)
-- Ajustes para tags, região denormalizada e fila de compartilhamento
-- WhatsApp (estrutura apenas; envio não implementado neste bloco).
-- IDEMPOTENTE: pode rodar mais de uma vez sem erro.
-- Em instalação nova, o database.sql já contém tudo isto.
-- =====================================================================

DROP PROCEDURE IF EXISTS sgg_add_col;
DROP PROCEDURE IF EXISTS sgg_add_idx;
DROP PROCEDURE IF EXISTS sgg_add_fk;
DELIMITER //
CREATE PROCEDURE sgg_add_col(IN tbl VARCHAR(64), IN col VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name=tbl AND column_name=col) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD COLUMN ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
CREATE PROCEDURE sgg_add_idx(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
    WHERE table_schema=DATABASE() AND table_name=tbl AND index_name=idx) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
CREATE PROCEDURE sgg_add_fk(IN tbl VARCHAR(64), IN fk VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
    WHERE table_schema=DATABASE() AND table_name=tbl AND constraint_name=fk) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD CONSTRAINT `',fk,'` ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
DELIMITER ;

-- ---- região denormalizada em media_files (dashboard territorial) ----
CALL sgg_add_col('media_files','region_id',"region_id BIGINT UNSIGNED NULL AFTER neighborhood_id");
CALL sgg_add_idx('media_files','idx_media_region','KEY idx_media_region (region_id)');
CALL sgg_add_fk('media_files','fk_media_region',
  "FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL");

-- ---- tags de mídia (N:N) ----
CREATE TABLE IF NOT EXISTS media_tags (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL DEFAULT 1,
  name       VARCHAR(60)  NOT NULL,
  slug       VARCHAR(60)  NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mediatag_slug_tenant (tenant_id, slug),
  KEY idx_mediatag_tenant (tenant_id),
  CONSTRAINT fk_mediatag_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_file_tags (
  media_file_id BIGINT UNSIGNED NOT NULL,
  tag_id        BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (media_file_id, tag_id),
  KEY idx_mft_tag (tag_id),
  CONSTRAINT fk_mft_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE,
  CONSTRAINT fk_mft_tag   FOREIGN KEY (tag_id)        REFERENCES media_tags(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- fila de compartilhamento WhatsApp (estrutura; SEM envio) ----
CREATE TABLE IF NOT EXISTS media_share_queue (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL DEFAULT 1,
  media_file_id BIGINT UNSIGNED NOT NULL,
  channel       ENUM('whatsapp','outro') NOT NULL DEFAULT 'whatsapp',
  status        ENUM('selecionado','preparado','enviado','cancelado') NOT NULL DEFAULT 'selecionado',
  note          VARCHAR(255) NULL,
  selected_by   BIGINT UNSIGNED NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_msq_tenant (tenant_id),
  KEY idx_msq_status (status),
  KEY idx_msq_media (media_file_id),
  CONSTRAINT fk_msq_tenant FOREIGN KEY (tenant_id)     REFERENCES tenants(id)     ON DELETE CASCADE,
  CONSTRAINT fk_msq_media  FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE,
  CONSTRAINT fk_msq_user   FOREIGN KEY (selected_by)   REFERENCES users(id)       ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP PROCEDURE IF EXISTS sgg_add_col;
DROP PROCEDURE IF EXISTS sgg_add_idx;
DROP PROCEDURE IF EXISTS sgg_add_fk;