-- NearSupply v2 schema (MySQL)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  username VARCHAR(60) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) DEFAULT NULL,
  role ENUM('admin','company','client') NOT NULL DEFAULT 'company',
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  last_login_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id CHAR(36) PRIMARY KEY,
  ip_address VARCHAR(64) NOT NULL,
  email VARCHAR(190) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_login_attempts_ip_email(ip_address, email),
  INDEX idx_login_attempts_time(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id CHAR(36) PRIMARY KEY,
  slug VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  entitlements_json JSON NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  plan_id CHAR(36) NOT NULL,
  status ENUM('active','trialing','past_due','canceled') NOT NULL DEFAULT 'active',
  current_period_start DATE NOT NULL,
  current_period_end DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sub_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY(plan_id) REFERENCES subscription_plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS companies (
  id CHAR(36) PRIMARY KEY,
  owner_user_id CHAR(36) NOT NULL,
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  type ENUM('supplier','client') NOT NULL DEFAULT 'supplier',
  description TEXT NULL,
  website VARCHAR(255) NULL,
  phone VARCHAR(60) NULL,
  email VARCHAR(190) NULL,
  logo_url VARCHAR(255) NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  approval_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_company_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_locations (
  id CHAR(36) PRIMARY KEY,
  company_id CHAR(36) NOT NULL,
  label VARCHAR(120) NULL,
  address VARCHAR(255) NULL,
  w3w VARCHAR(100) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_loc_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed plans (free + pro)
INSERT INTO subscription_plans (id, slug, name, price_monthly, currency, entitlements_json, created_at)
VALUES (UUID(), 'free', 'Free', 0, 'USD', JSON_OBJECT('company_pages', false, 'products', false, 'multiple_locations', false), NOW())
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO subscription_plans (id, slug, name, price_monthly, currency, entitlements_json, created_at)
VALUES (UUID(), 'pro', 'Pro', 29, 'USD', JSON_OBJECT('company_pages', true, 'products', true, 'multiple_locations', true), NOW())
ON DUPLICATE KEY UPDATE name=VALUES(name);

SET FOREIGN_KEY_CHECKS=1;