-- ============================================================
-- AMNAT AI - AI Smart Bio & Conversational Sales Platform
-- File: schema.sql
-- Compatible with MySQL 5.7+/8.0+ and common MariaDB on cPanel
-- Import into an EMPTY database selected in phpMyAdmin.
-- This file creates exactly THREE tables.
-- ============================================================

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET time_zone = '+07:00';

-- ------------------------------------------------------------
-- 1. Business owners and their AI Smart Bio configuration
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `owner_name` VARCHAR(150) NOT NULL,
    `owner_title` VARCHAR(150) DEFAULT '',
    `bio` TEXT,
    `avatar_url` VARCHAR(500) DEFAULT '',
    `phone` VARCHAR(50) DEFAULT '',
    `line_id` VARCHAR(100) DEFAULT '',
    `is_verified` TINYINT(1) DEFAULT 1,
    `package_tier` VARCHAR(50) DEFAULT 'Pro Max 74,890',
    `store_mode` ENUM('lead_gen', 'shopping_cart') DEFAULT 'lead_gen',
    `ai_language` VARCHAR(10) DEFAULT 'th-TH',
    `bot_name` VARCHAR(100) DEFAULT 'เอวา (AI Assistant)',
    `bot_persona` TEXT,
    `knowledge_base` TEXT,
    `points_balance` INT DEFAULT 3000,
    `account_status` ENUM('active', 'suspended') DEFAULT 'active',
    `hide_branding` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_slug` (`slug`),
    UNIQUE KEY `uq_users_email` (`email`),
    KEY `idx_users_account_status` (`account_status`),
    KEY `idx_users_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. Products, luxury properties, and external checkout links
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `catalog_items` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `price_label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `image_url` VARCHAR(500) DEFAULT '',
    `external_checkout_url` VARCHAR(500) DEFAULT '',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_catalog_user_id` (`user_id`),
    KEY `idx_catalog_user_created` (`user_id`, `created_at`),
    CONSTRAINT `fk_catalog_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. Sales leads and preliminary cart orders
--    cart_order: interest_details stores a JSON order snapshot.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `leads` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `customer_name` VARCHAR(150) DEFAULT 'ลูกค้าจากหน้าเว็บ',
    `customer_contact` VARCHAR(150) NOT NULL,
    `lead_type` ENUM('chat_lead', 'quick_form', 'cart_order') DEFAULT 'chat_lead',
    `interest_details` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_leads_user_id` (`user_id`),
    KEY `idx_leads_user_created` (`user_id`, `created_at`),
    KEY `idx_leads_user_type` (`user_id`, `lead_type`),
    CONSTRAINT `fk_leads_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SAMPLE DATA (DEMO ONLY; replace credentials before launch)
-- Password for both sample users: 123456
-- bcrypt value generated using PHP password_hash('123456', PASSWORD_DEFAULT)
-- No real customers, verified claims, or transaction data.
-- INSERT IGNORE prevents duplicate demo users on a second import.
-- ============================================================

START TRANSACTION;

INSERT IGNORE INTO `users` (
    `slug`, `email`, `password_hash`, `owner_name`, `owner_title`,
    `bio`, `avatar_url`, `phone`, `line_id`, `is_verified`,
    `package_tier`, `store_mode`, `ai_language`, `bot_name`,
    `bot_persona`, `knowledge_base`, `points_balance`,
    `account_status`, `hide_branding`
) VALUES
(
    'luxury_estate',
    'luxury@example.com',
    '$2y$12$..H80/RZPP9q8eUj6SUol.3pHb0PLQsF71NUpHCp.7/QJkrq.v3d.',
    'พิมพ์ชนก (บัญชีตัวอย่าง)',
    'ที่ปรึกษาอสังหาริมทรัพย์ระดับลักชัวรี',
    'ให้คำปรึกษาการเลือกเพนต์เฮาส์ คอนโดมิเนียม และบ้านหรู โดยเน้นความต้องการ งบประมาณ และรูปแบบการใช้ชีวิตของลูกค้า รายการทั้งหมดเป็นข้อมูลสาธิต ไม่ใช่ประกาศขายจริง',
    '',
    '020000001',
    '@luxury-estate-demo',
    1,
    'Pro Max 74,890',
    'lead_gen',
    'th-TH',
    'เอวา (AI Assistant)',
    'คุณคือเอวา ผู้ช่วยที่ปรึกษาอสังหาริมทรัพย์ พูดภาษาไทยสุภาพ เป็นธรรมชาติ ถามความต้องการและงบประมาณก่อนแนะนำ ไม่เร่งรัด ไม่กล่าวอ้างผลตอบแทนการลงทุน ไม่ยืนยันว่ารายการยังว่างโดยไม่มีข้อมูลล่าสุด และขอช่องทางติดต่อเมื่อได้รับความยินยอม',
    'นี่คือร้านตัวอย่าง Luxury Estate รายการและราคาทั้งหมดเป็นข้อมูลสาธิต ลูกค้าสนใจบ้านหรูหรือคอนโดสามารถให้ชื่อและเบอร์โทรหรือ LINE ID เพื่อให้เจ้าหน้าที่ติดต่อกลับได้ โดยต้องขอความยินยอมก่อนเก็บข้อมูล ติดต่อร้านผ่านเบอร์และ LINE ที่แสดงในโปรไฟล์เท่านั้น',
    3000,
    'active',
    0
),
(
    'aura_beauty',
    'aura@example.com',
    '$2y$12$..H80/RZPP9q8eUj6SUol.3pHb0PLQsF71NUpHCp.7/QJkrq.v3d.',
    'อรทัย (บัญชีตัวอย่าง)',
    'ผู้ก่อตั้งแบรนด์ Aura Beauty & Fashion',
    'แบรนด์ตัวอย่างสำหรับการสาธิตสินค้าบำรุงผิวและแฟชั่นพรีเมียม เน้นแนะนำสินค้าที่ตรงกับความต้องการและใช้งานหน้า AI Smart Bio แบบตะกร้าสินค้า',
    '',
    '020000002',
    '@aura-beauty-demo',
    1,
    'Pro Max 74,890',
    'shopping_cart',
    'th-TH',
    'ออร่า (AI Assistant)',
    'คุณคือออร่า ผู้ช่วยเลือกสินค้าและแนะนำแฟชั่น พูดภาษาไทยอย่างอบอุ่น กระชับ และไม่กดดันลูกค้า อธิบายเฉพาะข้อมูลสินค้าที่มีอยู่จริง ไม่กล่าวอ้างรักษาโรคหรือรับประกันผลลัพธ์ เมื่อลูกค้าใส่ตะกร้าให้ช่วยสรุปรายการและยอดรวมตามราคาจากระบบ',
    'นี่คือร้านตัวอย่าง Aura Beauty & Fashion สินค้าและราคาเป็นข้อมูลสาธิต ระบบรองรับตะกร้าสินค้า การสอบถามรายละเอียด การส่งคำสั่งซื้อ และการเก็บข้อมูลติดต่อเมื่อผู้ซื้อยินยอม ห้ามรับรองผลลัพธ์ด้านสุขภาพหรือสัญญาคุณสมบัติที่ไม่ได้ระบุ',
    3000,
    'active',
    0
);

-- ------------------------------------------------------------
-- Three demo luxury properties
-- ------------------------------------------------------------
INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'Riverside Signature Penthouse', 68900000.00, '68.9 ล้านบาท',
       'เพนต์เฮาส์ตัวอย่างพร้อมพื้นที่รับแขกขนาดใหญ่ เหมาะสำหรับผู้ที่มองหาความเป็นส่วนตัว ราคาและรายละเอียดใช้เพื่อสาธิตระบบเท่านั้น', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'luxury_estate'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'Riverside Signature Penthouse'
  );

INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'Grand Private Pool Villa', 42500000.00, '42.5 ล้านบาท',
       'บ้านพูลวิลล่าตัวอย่างพร้อมพื้นที่พักผ่อนส่วนตัว เหมาะกับครอบครัวที่ต้องการพื้นที่ใช้สอยมากขึ้น กรุณาตรวจสอบรายละเอียดจริงก่อนเสนอขาย', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'luxury_estate'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'Grand Private Pool Villa'
  );

INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'City Center Luxury Residence', 25900000.00, '25.9 ล้านบาท',
       'เรสซิเดนซ์ตัวอย่างที่ออกแบบสำหรับไลฟ์สไตล์เมือง รายละเอียดพื้นที่ สิ่งอำนวยความสะดวก และราคาควรยืนยันกับเจ้าของโครงการก่อนทำธุรกรรม', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'luxury_estate'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'City Center Luxury Residence'
  );

-- ------------------------------------------------------------
-- Three demo beauty/fashion products
-- ------------------------------------------------------------
INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'Aura Radiance Serum', 1590.00, '1,590 บาท',
       'เซรั่มบำรุงผิวตัวอย่าง เนื้อสัมผัสบางเบา เหมาะสำหรับสาธิตการแนะนำสินค้า ตรวจสอบส่วนผสมและข้อมูลฉลากจริงก่อนจำหน่าย', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'aura_beauty'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'Aura Radiance Serum'
  );

INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'Aura Velvet Moisture Cream', 1290.00, '1,290 บาท',
       'ครีมบำรุงผิวตัวอย่างสำหรับสาธิตระบบช้อปปิ้ง แนะนำให้ตรวจสอบสภาพผิวและรายละเอียดผลิตภัณฑ์ก่อนตัดสินใจ', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'aura_beauty'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'Aura Velvet Moisture Cream'
  );

INSERT INTO `catalog_items` (`user_id`, `title`, `price`, `price_label`, `description`, `image_url`, `external_checkout_url`)
SELECT `u`.`id`, 'Aura Signature Evening Dress', 2490.00, '2,490 บาท',
       'ชุดเดรสแฟชั่นพรีเมียมตัวอย่าง เหมาะกับงานเลี้ยงหรืองานทางการ เลือกไซซ์และสีตามตัวเลือกสินค้าจริงก่อนยืนยันคำสั่งซื้อ', '', ''
FROM `users` AS `u`
WHERE `u`.`slug` = 'aura_beauty'
  AND NOT EXISTS (
      SELECT 1 FROM `catalog_items` AS `c`
      WHERE `c`.`user_id` = `u`.`id` AND `c`.`title` = 'Aura Signature Evening Dress'
  );

COMMIT;

-- ------------------------------------------------------------
-- Optional verification queries (run manually in phpMyAdmin):
-- SELECT id, slug, email, store_mode, points_balance FROM users;
-- SELECT user_id, COUNT(*) AS items FROM catalog_items GROUP BY user_id;
-- ------------------------------------------------------------