-- AgriNeum / AgroConnect - MySQL schema (core tables for PHP backend)
-- Run this in MySQL/MariaDB to create the database and core tables.
-- Add more tables as you migrate from Supabase (see MIGRATION.md).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS agroneum CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE agroneum;

-- -----------------------------------------------
-- users (replaces Supabase auth.users + public.users)
-- -----------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) NOT NULL PRIMARY KEY,
  email VARCHAR(254) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL DEFAULT '',
  last_name VARCHAR(100) NOT NULL DEFAULT '',
  phone VARCHAR(50) NULL,
  user_type VARCHAR(50) NOT NULL DEFAULT 'buyer',
  verification_status VARCHAR(50) NOT NULL DEFAULT 'pending',
  seller_status VARCHAR(50) NOT NULL DEFAULT 'inactive',
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  phone_verified TINYINT(1) NOT NULL DEFAULT 0,
  id_verified TINYINT(1) NOT NULL DEFAULT 0,
  reviews_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_sales DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_purchases DECIMAL(14,2) NOT NULL DEFAULT 0,
  roles JSON NULL COMMENT 'e.g. ["admin"]',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_users_email (email),
  KEY idx_users_user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------
-- contact_submissions (contact form)
-- -----------------------------------------------
CREATE TABLE IF NOT EXISTS contact_submissions (
  id CHAR(36) NOT NULL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  email VARCHAR(254) NOT NULL,
  phone VARCHAR(50) NULL,
  category VARCHAR(100) NULL,
  priority VARCHAR(50) NULL,
  subject VARCHAR(500) NOT NULL,
  message TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new'
    CHECK (status IN ('new','read','replied','archived')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_contact_submissions_created_at (created_at DESC),
  KEY idx_contact_submissions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------
-- platform_settings (brand, commission, etc.)
-- -----------------------------------------------
CREATE TABLE IF NOT EXISTS platform_settings (
  id CHAR(36) NOT NULL PRIMARY KEY,
  key_name VARCHAR(100) NOT NULL UNIQUE,
  value_json JSON NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------
-- cms_pages
-- -----------------------------------------------
CREATE TABLE IF NOT EXISTS cms_pages (
  id CHAR(36) NOT NULL PRIMARY KEY,
  slug VARCHAR(255) NOT NULL UNIQUE,
  title VARCHAR(500) NOT NULL,
  html_content LONGTEXT NULL,
  meta_description VARCHAR(500) NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  created_by CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by CHAR(36) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cms_pages_slug (slug),
  KEY idx_cms_pages_published (is_published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------
-- announcements
-- -----------------------------------------------
CREATE TABLE IF NOT EXISTS announcements (
  id CHAR(36) NOT NULL PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  content TEXT NOT NULL,
  type VARCHAR(50) NOT NULL DEFAULT 'notice',
  status VARCHAR(50) NOT NULL DEFAULT 'draft',
  scheduled_for TIMESTAMP NULL,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  created_by CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_announcements_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
