-- ============================================================
-- 0001_tenants_feedback_testimonials.sql
-- Version: v1.0.0000
-- Adds the core multi-tenant tables for The Client Speaks:
-- tenants (Feedback Receivers), feedback (quick ratings), and
-- testimonials (marketing-usable quotes with consent).
-- Run this in phpMyAdmin against the DEV database first
-- (theclientspeaks_dev-app), confirm, then run against prod later.
-- CHANGELOG:
-- v1.0.0000 — Initial build
-- ============================================================

-- --------------------------------------------------------
-- tenants — one row per Feedback Receiver account
-- --------------------------------------------------------
CREATE TABLE `tenants` (
  `id` int NOT NULL AUTO_INCREMENT,
  `owner_user_id` int NOT NULL,
  `business_name` varchar(255) NOT NULL,
  `subdomain` varchar(100) NOT NULL,
  `api_key` varchar(64) NOT NULL,
  `api_key_created_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_subdomain` (`subdomain`),
  UNIQUE KEY `uniq_api_key` (`api_key`),
  KEY `idx_owner_user_id` (`owner_user_id`),
  CONSTRAINT `fk_tenants_owner_user` FOREIGN KEY (`owner_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- feedback — quick thumbs/scale ratings, no consent needed,
-- private/internal use only
-- --------------------------------------------------------
CREATE TABLE `feedback` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `source_type` enum('widget','hosted_page','api') NOT NULL,
  `rating` varchar(20) NOT NULL,
  `free_text` text,
  `context_ref` varchar(255) DEFAULT NULL,
  `ip_hash` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_created` (`tenant_id`, `created_at`),
  CONSTRAINT `fk_feedback_tenant` FOREIGN KEY (`tenant_id`)
    REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- testimonials — marketing-usable quotes; consent is captured
-- against approved_text (the final version), not the raw submission
-- --------------------------------------------------------
CREATE TABLE `testimonials` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `source_type` enum('widget','hosted_page','api') NOT NULL,
  `provider_name` varchar(255) DEFAULT NULL,
  `original_text` text NOT NULL,
  `ai_rewritten_text` text,
  `approved_text` text,
  `photo_url` varchar(500) DEFAULT NULL,
  `consent_to_publish` tinyint(1) NOT NULL DEFAULT 0,
  `consent_at` datetime DEFAULT NULL,
  `receiver_approved` tinyint(1) NOT NULL DEFAULT 0,
  `ip_hash` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_created` (`tenant_id`, `created_at`),
  CONSTRAINT `fk_testimonials_tenant` FOREIGN KEY (`tenant_id`)
    REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;