CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('customer','admin','manager') DEFAULT 'customer',
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS auth_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_auth_tokens_user_expiry(user_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cart_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  guest_session_id VARCHAR(80) NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  INDEX idx_cart_user(user_id),
  INDEX idx_cart_guest(guest_session_id),
  UNIQUE KEY uniq_cart_user_product(user_id, product_id),
  UNIQUE KEY uniq_cart_guest_product(guest_session_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS addresses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  full_name VARCHAR(50) NOT NULL,
  phone CHAR(10) NOT NULL,
  address_line1 VARCHAR(100) NOT NULL,
  address_line2 VARCHAR(100) NULL,
  landmark VARCHAR(50) NULL,
  pincode CHAR(6) NOT NULL,
  city VARCHAR(80) NOT NULL,
  state VARCHAR(80) NOT NULL,
  country VARCHAR(80) NOT NULL DEFAULT 'India',
  is_default TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_addresses_user_default(user_id, is_default),
  INDEX idx_addresses_pincode(pincode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  provider VARCHAR(60) NOT NULL DEFAULT 'razorpay',
  provider_order_id VARCHAR(120) NULL,
  provider_payment_id VARCHAR(120) NULL,
  provider_signature VARCHAR(255) NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  currency CHAR(3) DEFAULT 'INR',
  status ENUM('created','captured','failed','refunded') DEFAULT 'created',
  raw_response JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_payment_provider_payment(provider_payment_id),
  INDEX idx_payments_order(order_id),
  INDEX idx_payments_provider_order(provider_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_timeline (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  status VARCHAR(80) NOT NULL,
  title VARCHAR(160) NOT NULL,
  note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_timeline_order(order_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_zones (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pincode CHAR(6) NOT NULL UNIQUE,
  city VARCHAR(80) NOT NULL,
  state VARCHAR(80) NOT NULL,
  country VARCHAR(80) NOT NULL DEFAULT 'India',
  delivery_charge DECIMAL(10,2) NOT NULL DEFAULT 50,
  delivery_estimate_days INT NOT NULL DEFAULT 5,
  is_serviceable TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE orders ADD COLUMN user_id INT NULL AFTER id;
ALTER TABLE orders ADD COLUMN address_id INT NULL AFTER customer_phone;
ALTER TABLE orders ADD COLUMN address_snapshot JSON NULL AFTER pincode;
ALTER TABLE orders ADD COLUMN tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_amount;
ALTER TABLE orders ADD COLUMN delivery_estimate_date DATE NULL AFTER total_amount;
ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(120) NULL AFTER razorpay_payment_id;
ALTER TABLE orders ADD COLUMN courier_partner VARCHAR(120) NULL AFTER tracking_number;
ALTER TABLE orders ADD COLUMN shipment_status VARCHAR(120) NULL AFTER courier_partner;
ALTER TABLE orders MODIFY order_status ENUM('payment_pending','confirmed','processing','packed','shipped','out_for_delivery','delivered','cancelled','returned','refunded') DEFAULT 'confirmed';
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

INSERT IGNORE INTO users(name,email,phone,password_hash,role,status) VALUES
('Admin','admin@anshulfoodproduct.com','9999999999','$2y$10$GqdB9tE8ShV/2wNAK4efLOIfxGILYiVGzH7OaY3OJW/iGNJLdaS1G','admin','active');

INSERT IGNORE INTO delivery_zones(pincode, city, state, country, delivery_charge, delivery_estimate_days, is_serviceable) VALUES
('422001','Nashik','Maharashtra','India',40,3,1),
('400001','Mumbai','Maharashtra','India',60,4,1),
('411001','Pune','Maharashtra','India',60,4,1),
('110001','Delhi','Delhi','India',90,6,1);

CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_razorpay_order ON orders(razorpay_order_id);
CREATE INDEX idx_orders_razorpay_payment ON orders(razorpay_payment_id);
CREATE INDEX idx_orders_customer_email ON orders(customer_email);
CREATE INDEX idx_delivery_zones_serviceable ON delivery_zones(pincode, is_serviceable);