-- ============================================================
-- Ragreen MVP - Database Schema
-- Engine: MySQL 8.x  |  Charset: utf8mb4
--
-- SHARED HOSTING / phpMyAdmin (recommended for this deployment):
--   Your database is already created with your host's naming (e.g.
--   playpulse_ragreen) - shared-hosting MySQL users usually can't create
--   new databases, only use the ones already assigned to them. In
--   phpMyAdmin, SELECT that existing database in the left sidebar first,
--   THEN use Import -> this file. Do not run this file against phpMyAdmin's
--   "SQL" tab without selecting your database first, or it may try to
--   create/use a differently-named database instead of yours.
--
-- LOCAL DEV (XAMPP/MAMP, full root access):
--   mysql -u root -p -e "CREATE DATABASE ragreen CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
--   mysql -u root -p ragreen < schema.sql
-- ============================================================

-- ------------------------------------------------------------
-- 1. USERS & PROFILES
-- ------------------------------------------------------------

CREATE TABLE users (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email               VARCHAR(191) NOT NULL UNIQUE,
    password_hash       VARCHAR(255) NULL,
    phone               VARCHAR(30)  DEFAULT NULL,
    account_type        ENUM('personal', 'business', 'admin') NOT NULL DEFAULT 'personal',
    status              ENUM('active', 'suspended', 'pending') NOT NULL DEFAULT 'active',
    oauth_provider      VARCHAR(20) DEFAULT NULL, -- 'google' | 'facebook' | NULL
    oauth_id            VARCHAR(191) DEFAULT NULL,
    email_verified_at   DATETIME DEFAULT NULL,
    is_featured_vendor  TINYINT(1) NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_users_oauth (oauth_provider, oauth_id)
) ENGINE=InnoDB;

CREATE TABLE password_resets (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    token_hash  VARCHAR(255) NOT NULL,
    expires_at  DATETIME NOT NULL,
    used_at     DATETIME DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE email_verifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    token_hash  VARCHAR(255) NOT NULL,
    expires_at  DATETIME NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE personal_profiles (
    user_id      INT UNSIGNED PRIMARY KEY,
    full_name    VARCHAR(150) NOT NULL,
    avatar_path  VARCHAR(255) DEFAULT NULL,
    address      VARCHAR(255) DEFAULT NULL,
    city         VARCHAR(100) DEFAULT NULL,
    country      VARCHAR(100) DEFAULT NULL,
    latitude     DECIMAL(10,7) DEFAULT NULL,
    longitude    DECIMAL(10,7) DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE business_profiles (
    user_id         INT UNSIGNED PRIMARY KEY,
    business_name   VARCHAR(150) NOT NULL,
    category        VARCHAR(100) DEFAULT NULL,
    logo_path       VARCHAR(255) DEFAULT NULL,
    contact_person  VARCHAR(150) DEFAULT NULL,
    address         VARCHAR(255) DEFAULT NULL,
    city            VARCHAR(100) DEFAULT NULL,
    country         VARCHAR(100) DEFAULT NULL,
    latitude        DECIMAL(10,7) DEFAULT NULL,
    longitude       DECIMAL(10,7) DEFAULT NULL,
    subscription_status ENUM('trial','active','expired','cancelled') NOT NULL DEFAULT 'trial',
    subscription_plan   VARCHAR(64) DEFAULT NULL,
    trial_ends_at        DATE DEFAULT NULL,
    subscription_notes   TEXT DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 2. CATEGORIES & PRODUCTS
-- ------------------------------------------------------------

CREATE TABLE categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id   INT UNSIGNED DEFAULT NULL,
    name        VARCHAR(100) NOT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE products (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id        INT UNSIGNED NOT NULL,
    category_id     INT UNSIGNED DEFAULT NULL,
    barcode         VARCHAR(64) DEFAULT NULL,
    name            VARCHAR(191) NOT NULL,
    description     TEXT,
    quantity        INT UNSIGNED NOT NULL DEFAULT 1,
    original_price  DECIMAL(10,2) DEFAULT NULL,
    selling_price   DECIMAL(10,2) NOT NULL,
    expiry_date     DATE DEFAULT NULL,
    location        VARCHAR(191) DEFAULT NULL,
    city            VARCHAR(100) DEFAULT NULL,
    country         VARCHAR(100) DEFAULT NULL,
    status          ENUM('active', 'inactive', 'expired', 'sold') NOT NULL DEFAULT 'active',
    is_featured     TINYINT(1) NOT NULL DEFAULT 0,
    featured_until  DATE DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_products_status (status),
    INDEX idx_products_expiry (expiry_date),
    INDEX idx_products_barcode (barcode),
    INDEX idx_products_geo (country, city)
) ENGINE=InnoDB;

CREATE TABLE product_images (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id  INT UNSIGNED NOT NULL,
    image_path  VARCHAR(255) NOT NULL,
    sort_order  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 3. CART & ORDERS (no payment gateway - MVP scope)
-- ------------------------------------------------------------

CREATE TABLE cart_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    product_id  INT UNSIGNED NOT NULL,
    quantity    INT UNSIGNED NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_cart_user_product (user_id, product_id)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyer_id     INT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    status       ENUM('pending', 'confirmed', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id    INT UNSIGNED NOT NULL,
    product_id  INT UNSIGNED NOT NULL,
    seller_id   INT UNSIGNED NOT NULL,
    quantity    INT UNSIGNED NOT NULL DEFAULT 1,
    unit_price  DECIMAL(10,2) NOT NULL,
    status      ENUM('pending', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'pending',
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 4. PANTRY & LOYALTY
-- ------------------------------------------------------------

CREATE TABLE pantry_items (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id           INT UNSIGNED NOT NULL,
    source_product_id INT UNSIGNED DEFAULT NULL,
    product_name      VARCHAR(191) NOT NULL,
    quantity          INT UNSIGNED NOT NULL DEFAULT 1,
    expiry_date       DATE DEFAULT NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (source_product_id) REFERENCES products(id) ON DELETE SET NULL,
    INDEX idx_pantry_expiry (expiry_date)
) ENGINE=InnoDB;

CREATE TABLE loyalty_points (
    user_id         INT UNSIGNED PRIMARY KEY,
    points_balance  INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE loyalty_transactions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    points      INT NOT NULL,
    reason      VARCHAR(191) NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 5. NOTIFICATIONS
-- ------------------------------------------------------------

CREATE TABLE notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    type        VARCHAR(50) NOT NULL,
    message     VARCHAR(255) NOT NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user_read (user_id, is_read)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 6. MODERATION / ADMIN
-- ------------------------------------------------------------

CREATE TABLE reports (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reported_by  INT UNSIGNED NOT NULL,
    product_id   INT UNSIGNED DEFAULT NULL,
    reported_user_id INT UNSIGNED DEFAULT NULL,
    reason       VARCHAR(255) NOT NULL,
    status       ENUM('open', 'reviewing', 'resolved', 'dismissed') NOT NULL DEFAULT 'open',
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 6b. PRODUCT REVIEWS & RATINGS
-- ------------------------------------------------------------

CREATE TABLE product_reviews (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    reviewer_id  INT UNSIGNED NOT NULL,
    order_id     INT UNSIGNED NOT NULL,
    rating       TINYINT UNSIGNED NOT NULL,
    comment      TEXT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_review_per_product_reviewer (product_id, reviewer_id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 6c. ITEM REQUESTS ("wanted" board)
-- ------------------------------------------------------------

CREATE TABLE item_requests (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_id      INT UNSIGNED NOT NULL,
    item_name         VARCHAR(191) NOT NULL,
    category_id       INT UNSIGNED DEFAULT NULL,
    description       TEXT,
    desired_quantity  INT UNSIGNED NOT NULL DEFAULT 1,
    status            ENUM('open', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'open',
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 7. SETTINGS (admin-configurable: rewards formula, market geo scope, OAuth keys)
-- ------------------------------------------------------------

CREATE TABLE settings (
    setting_key   VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
  ('points_per_dollar_spent', '1'),
  ('points_per_new_listing', '2'),
  ('market_country', ''),
  ('market_city', ''),
  ('oauth_google_client_id', ''),
  ('oauth_google_client_secret', ''),
  ('oauth_facebook_app_id', ''),
  ('oauth_facebook_app_secret', ''),
  ('smtp_host', ''),
  ('smtp_port', '587'),
  ('smtp_username', ''),
  ('smtp_password', ''),
  ('smtp_encryption', 'tls'),
  ('smtp_from_email', ''),
  ('smtp_from_name', 'Ragreen'),
  ('adsense_client_id', ''),
  ('business_trial_days', '90'),
  ('subscriptions_enabled', '0');

CREATE TABLE ad_placements (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    placement_key     VARCHAR(64) NOT NULL,
    ad_type           ENUM('google', 'custom') NOT NULL,
    width             SMALLINT UNSIGNED NOT NULL,
    height            SMALLINT UNSIGNED NOT NULL,
    google_ad_slot    VARCHAR(64) DEFAULT NULL,
    is_test_mode      TINYINT(1) NOT NULL DEFAULT 0,
    custom_image_path VARCHAR(255) DEFAULT NULL,
    custom_link_url   VARCHAR(500) DEFAULT NULL,
    custom_alt_text   VARCHAR(255) DEFAULT NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    display_order     INT NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_placement_active (placement_key, is_active)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 8. REWARDS CATALOG & REDEMPTIONS
-- ------------------------------------------------------------

CREATE TABLE rewards (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(150) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    points_cost INT UNSIGNED NOT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE reward_redemptions (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          INT UNSIGNED NOT NULL,
    reward_id        INT UNSIGNED NOT NULL,
    points_spent     INT UNSIGNED NOT NULL,
    redemption_code  VARCHAR(20) NOT NULL UNIQUE,
    status           ENUM('pending', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reward_id) REFERENCES rewards(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO rewards (title, description, points_cost) VALUES
  ('$2 off your next order', 'Show the code to any seller at pickup.', 20),
  ('$5 off your next order', 'Show the code to any seller at pickup.', 45),
  ('Featured listing boost (7 days)', 'Business accounts only - highlights your listing.', 60);

-- ------------------------------------------------------------
-- 9. BULK CSV IMPORT LOG
-- ------------------------------------------------------------

CREATE TABLE product_import_batches (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id        INT UNSIGNED NOT NULL,
    filename        VARCHAR(255) NOT NULL,
    rows_total      INT UNSIGNED NOT NULL DEFAULT 0,
    rows_created    INT UNSIGNED NOT NULL DEFAULT 0,
    rows_failed     INT UNSIGNED NOT NULL DEFAULT 0,
    error_summary   TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Seed: base categories
-- ------------------------------------------------------------
INSERT INTO categories (name) VALUES
  ('Bakery'), ('Dairy & Eggs'), ('Fruits & Vegetables'),
  ('Canned & Packaged'), ('Beverages'), ('Frozen Food'), ('Other');
