-- Table for landing page enquiries (fpageantsbyftv.ae)
-- Run this if creating the table from scratch.

CREATE TABLE IF NOT EXISTS enquires (
  id INT AUTO_INCREMENT PRIMARY KEY,
  unique_code VARCHAR(20) NOT NULL UNIQUE,
  fullname VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  contact VARCHAR(10) NOT NULL,
  instagram_url VARCHAR(500) NOT NULL,
  date_of_birth DATE NOT NULL,
  age INT NOT NULL,
  nationality VARCHAR(50) NOT NULL,
  state VARCHAR(50) NOT NULL,
  city VARCHAR(50) NOT NULL,
  latitude DECIMAL(10, 8) NOT NULL,
  longitude DECIMAL(11, 8) NOT NULL,
  location_display VARCHAR(255) NOT NULL,
  portfolio VARCHAR(1000) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_contact (contact),
  INDEX idx_email (email)
);

-- If you already have an "enquires" table with old columns (investment, message),
-- run this to add the new columns and then drop the old ones if desired:

-- ALTER TABLE enquires
--   ADD COLUMN instagram_url VARCHAR(500) AFTER contact,
--   ADD COLUMN date_of_birth DATE AFTER instagram_url,
--   ADD COLUMN age INT AFTER date_of_birth,
--   ADD COLUMN nationality VARCHAR(50) AFTER age,
--   ADD COLUMN latitude DECIMAL(10,8) AFTER city,
--   ADD COLUMN longitude DECIMAL(11,8) AFTER latitude,
--   ADD COLUMN location_display VARCHAR(255) AFTER longitude,
--   ADD COLUMN portfolio VARCHAR(1000) AFTER location_display;
-- ALTER TABLE enquires DROP COLUMN investment, DROP COLUMN message;
