-- MySQL schema for Furnify premium online store with Re-Up requests
-- Default admin: username admin, password Admin@12345
-- Change the password immediately after first login.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(60) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL DEFAULT 'Furnify Admin',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name VARCHAR(140) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  city VARCHAR(140) NULL,
  address VARCHAR(255) NULL,
  email_verified_at DATETIME NULL,
  phone_verified_at DATETIME NULL,
  email_verification_token_hash VARCHAR(255) NULL,
  email_verification_expires_at DATETIME NULL,
  reset_token_hash VARCHAR(255) NULL,
  reset_expires_at DATETIME NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_customers_email (email),
  INDEX idx_customers_status (status),
  INDEX idx_customers_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(80) NOT NULL,
  name VARCHAR(180) NOT NULL,
  tagline TEXT NULL,
  collection VARCHAR(80) NULL,
  room VARCHAR(120) NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  compare_at DECIMAL(12,2) NOT NULL DEFAULT 0,
  rating DECIMAL(3,2) NOT NULL DEFAULT 0,
  reviews INT UNSIGNED NOT NULL DEFAULT 0,
  stock_status VARCHAR(80) NULL,
  seats VARCHAR(80) NULL,
  dimensions VARCHAR(120) NULL,
  width_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  depth_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  height_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  material VARCHAR(220) NULL,
  color VARCHAR(120) NULL,
  lead_time VARCHAR(80) NULL,
  warranty VARCHAR(160) NULL,
  image VARCHAR(255) NULL,
  model VARCHAR(255) NULL,
  ar_floor_offset_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  badges_json TEXT NULL,
  features_json TEXT NULL,
  swatches_json TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_products_active (is_active),
  INDEX idx_products_collection (collection)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NULL,
  order_number VARCHAR(40) NOT NULL UNIQUE,
  customer_name VARCHAR(140) NOT NULL,
  customer_phone VARCHAR(40) NOT NULL,
  customer_email VARCHAR(180) NOT NULL,
  customer_city VARCHAR(140) NOT NULL,
  customer_address VARCHAR(255) NOT NULL,
  payment_method VARCHAR(80) NOT NULL,
  payment_status VARCHAR(80) NOT NULL DEFAULT 'Unpaid',
  payment_reference VARCHAR(140) NULL,
  payment_proof_path VARCHAR(255) NULL,
  payment_link VARCHAR(500) NULL,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  balance_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  delivery_method VARCHAR(80) NOT NULL,
  message TEXT NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  status VARCHAR(80) NOT NULL DEFAULT 'Pending quotation',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_orders_customer (customer_id),
  INDEX idx_orders_email (customer_email),
  INDEX idx_orders_created (created_at),
  INDEX idx_orders_status (status),
  INDEX idx_orders_payment_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id INT UNSIGNED NOT NULL,
  product_id VARCHAR(80) NOT NULL,
  name VARCHAR(180) NOT NULL,
  collection VARCHAR(80) NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  qty INT UNSIGNED NOT NULL DEFAULT 1,
  image VARCHAR(255) NULL,
  dimensions VARCHAR(120) NULL,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_order_items_order (order_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS reup_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NULL,
  request_number VARCHAR(40) NOT NULL UNIQUE,
  customer_name VARCHAR(140) NOT NULL,
  customer_phone VARCHAR(40) NOT NULL,
  customer_email VARCHAR(180) NULL,
  customer_city VARCHAR(140) NOT NULL,
  customer_address VARCHAR(255) NULL,
  service_type VARCHAR(80) NOT NULL,
  condition_level VARCHAR(80) NOT NULL,
  issue_json TEXT NULL,
  width_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  depth_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  height_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  seat_count INT UNSIGNED NOT NULL DEFAULT 1,
  fabric_preference VARCHAR(180) NULL,
  photo_path VARCHAR(255) NULL,
  message TEXT NULL,
  estimate_low DECIMAL(12,2) NOT NULL DEFAULT 0,
  estimate_high DECIMAL(12,2) NOT NULL DEFAULT 0,
  final_quote DECIMAL(12,2) NULL,
  admin_notes TEXT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'For review',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_reup_customer_id (customer_id),
  INDEX idx_reup_email (customer_email),
  INDEX idx_reup_created (created_at),
  INDEX idx_reup_status (status),
  INDEX idx_reup_customer (customer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS custom_sofa_quotes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NULL,
  request_number VARCHAR(40) NOT NULL UNIQUE,
  customer_name VARCHAR(140) NOT NULL,
  customer_phone VARCHAR(40) NOT NULL,
  customer_email VARCHAR(180) NULL,
  customer_city VARCHAR(140) NOT NULL,
  customer_address VARCHAR(255) NULL,
  sofa_type VARCHAR(80) NOT NULL,
  design_style VARCHAR(80) NOT NULL,
  material_preference VARCHAR(80) NOT NULL,
  color_preference VARCHAR(140) NULL,
  firmness VARCHAR(80) NOT NULL,
  feature_json TEXT NULL,
  width_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  depth_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  height_cm DECIMAL(8,2) NOT NULL DEFAULT 0,
  seat_count INT UNSIGNED NOT NULL DEFAULT 1,
  chaise_side VARCHAR(80) NULL,
  budget_range VARCHAR(100) NULL,
  timeline VARCHAR(100) NULL,
  inspiration_photo_path VARCHAR(255) NULL,
  room_photo_path VARCHAR(255) NULL,
  message TEXT NULL,
  estimate_low DECIMAL(12,2) NOT NULL DEFAULT 0,
  estimate_high DECIMAL(12,2) NOT NULL DEFAULT 0,
  final_quote DECIMAL(12,2) NULL,
  admin_notes TEXT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'For design review',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_custom_customer_id (customer_id),
  INDEX idx_custom_email (customer_email),
  INDEX idx_custom_created (created_at),
  INDEX idx_custom_status (status),
  INDEX idx_custom_customer (customer_name),
  INDEX idx_custom_type (sofa_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admins (username, password_hash, full_name) VALUES ('admin', '$2y$12$3ih3lRTIAliXxONDQ8j6jeszOlnigd7Zl.rGc7sVMH0bgd6xrBw6u', 'Furnify Admin') ON DUPLICATE KEY UPDATE username = VALUES(username);

INSERT INTO products (id, name, tagline, collection, room, price, compare_at, rating, reviews, stock_status, seats, dimensions, width_cm, depth_cm, height_cm, material, color, lead_time, warranty, image, model, ar_floor_offset_cm, badges_json, features_json, swatches_json, is_active, sort_order) VALUES ('luna-sectional', 'Luna Modular Chaise', 'Soft moss fabric with a right chaise layout for flexible family spaces.', 'Modular', 'Family living room', 54900, 62900, 4.9, 38, 'Made to order', '3 seats + chaise', '260cm W x 165cm D x 86cm H', 260, 165, 86, 'Performance weave fabric, kiln-dried frame', 'Moss green', '7-10 days', '1-year frame warranty', 'assets/images/sofa-luna.png', 'assets/models/sofa-luna.glb', 0, '["Best seller", "AR-ready", "Modular"]', '["Movable chaise section for flexible sala layouts", "Stain-resistant fabric for everyday family use", "Medium-soft cushions with removable covers"]', '[{"label": "Moss", "hex": "#55736c"}, {"label": "Fog", "hex": "#d9ddd7"}, {"label": "Charcoal", "hex": "#323936"}]', 1, 10) ON DUPLICATE KEY UPDATE name=VALUES(name), tagline=VALUES(tagline), collection=VALUES(collection), room=VALUES(room), price=VALUES(price), compare_at=VALUES(compare_at), rating=VALUES(rating), reviews=VALUES(reviews), stock_status=VALUES(stock_status), seats=VALUES(seats), dimensions=VALUES(dimensions), width_cm=VALUES(width_cm), depth_cm=VALUES(depth_cm), height_cm=VALUES(height_cm), material=VALUES(material), color=VALUES(color), lead_time=VALUES(lead_time), warranty=VALUES(warranty), image=VALUES(image), model=VALUES(model), ar_floor_offset_cm=VALUES(ar_floor_offset_cm), badges_json=VALUES(badges_json), features_json=VALUES(features_json), swatches_json=VALUES(swatches_json), is_active=VALUES(is_active), sort_order=VALUES(sort_order);

INSERT INTO products (id, name, tagline, collection, room, price, compare_at, rating, reviews, stock_status, seats, dimensions, width_cm, depth_cm, height_cm, material, color, lead_time, warranty, image, model, ar_floor_offset_cm, badges_json, features_json, swatches_json, is_active, sort_order) VALUES ('mika-loveseat', 'Mika Urban Loveseat', 'Compact comfort for condos, apartments, and smaller living rooms.', 'Compact', 'Condo / studio', 32900, 37900, 4.8, 24, 'Ready to reserve', '2 seats', '178cm W x 92cm D x 84cm H', 178, 92, 84, 'Textured linen blend, solid rubberwood legs', 'Terracotta clay', '5-8 days', '1-year service warranty', 'assets/images/sofa-mika.png', 'assets/models/sofa-mika.glb', 0, '["Condo fit", "AR-ready", "Space saver"]', '["Slim arms keep the seating area generous", "Raised legs make cleaning easier", "Warm fabric tone pairs well with wood and rattan"]', '[{"label": "Clay", "hex": "#b76f55"}, {"label": "Olive", "hex": "#6d8b74"}, {"label": "Ivory", "hex": "#eee7dc"}]', 1, 20) ON DUPLICATE KEY UPDATE name=VALUES(name), tagline=VALUES(tagline), collection=VALUES(collection), room=VALUES(room), price=VALUES(price), compare_at=VALUES(compare_at), rating=VALUES(rating), reviews=VALUES(reviews), stock_status=VALUES(stock_status), seats=VALUES(seats), dimensions=VALUES(dimensions), width_cm=VALUES(width_cm), depth_cm=VALUES(depth_cm), height_cm=VALUES(height_cm), material=VALUES(material), color=VALUES(color), lead_time=VALUES(lead_time), warranty=VALUES(warranty), image=VALUES(image), model=VALUES(model), ar_floor_offset_cm=VALUES(ar_floor_offset_cm), badges_json=VALUES(badges_json), features_json=VALUES(features_json), swatches_json=VALUES(swatches_json), is_active=VALUES(is_active), sort_order=VALUES(sort_order);

INSERT INTO products (id, name, tagline, collection, room, price, compare_at, rating, reviews, stock_status, seats, dimensions, width_cm, depth_cm, height_cm, material, color, lead_time, warranty, image, model, ar_floor_offset_cm, badges_json, features_json, swatches_json, is_active, sort_order) VALUES ('orion-sofa', 'Orion Low Profile Sofa', 'Clean lines, wide cushions, and a relaxed premium lounge feel.', 'Lounge', 'Entertainment room', 46900, 51900, 4.7, 31, 'Limited slots', '3 seats', '232cm W x 98cm D x 78cm H', 232, 98, 78, 'Premium twill fabric, black steel legs', 'Slate blue', '8-12 days', '1-year structural warranty', 'assets/images/sofa-orion.png', 'assets/models/sofa-orion.glb', 0, '["Low profile", "AR-ready", "Lounge depth"]', '["Lower back height keeps the room visually open", "Wide seat depth for lounging", "Durable base suited for daily use"]', '[{"label": "Slate", "hex": "#526479"}, {"label": "Sand", "hex": "#d8c8b1"}, {"label": "Forest", "hex": "#244b40"}]', 1, 30) ON DUPLICATE KEY UPDATE name=VALUES(name), tagline=VALUES(tagline), collection=VALUES(collection), room=VALUES(room), price=VALUES(price), compare_at=VALUES(compare_at), rating=VALUES(rating), reviews=VALUES(reviews), stock_status=VALUES(stock_status), seats=VALUES(seats), dimensions=VALUES(dimensions), width_cm=VALUES(width_cm), depth_cm=VALUES(depth_cm), height_cm=VALUES(height_cm), material=VALUES(material), color=VALUES(color), lead_time=VALUES(lead_time), warranty=VALUES(warranty), image=VALUES(image), model=VALUES(model), ar_floor_offset_cm=VALUES(ar_floor_offset_cm), badges_json=VALUES(badges_json), features_json=VALUES(features_json), swatches_json=VALUES(swatches_json), is_active=VALUES(is_active), sort_order=VALUES(sort_order);
