CREATE DATABASE IF NOT EXISTS peptide_os;
USE peptide_os;

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  full_name VARCHAR(100),
  birth_place VARCHAR(100),
  birth_date DATE,
  email VARCHAR(100),
  phone VARCHAR(30),
  profile_photo VARCHAR(500),
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('master_admin','cashier','supplier') DEFAULT 'cashier',
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(20) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  mg DECIMAL(10,2) DEFAULT 0,
  ml DECIMAL(10,2) DEFAULT 0,
  route VARCHAR(10) DEFAULT 'SQ',
  cost_price DECIMAL(10,2) DEFAULT 0,
  retail_price DECIMAL(10,2) DEFAULT 0,
  stock_qty INT DEFAULT 0,
  batch VARCHAR(50),
  expiry DATE,
  supplier VARCHAR(100),
  low_threshold INT DEFAULT 5,
  added_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Clients table
CREATE TABLE IF NOT EXISTS clients (
  id VARCHAR(20) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(30),
  email VARCHAR(100),
  address TEXT,
  allergies TEXT,
  notes TEXT,
  birth_date DATE,
  is_deleted TINYINT(1) DEFAULT 0,
  is_vip TINYINT(1) DEFAULT 0,
  referred_by VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (referred_by) REFERENCES clients(id) ON DELETE SET NULL
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(20) PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  subtotal DECIMAL(10,2) DEFAULT 0,
  discount_pct DECIMAL(5,2) DEFAULT 0,
  discount_amt DECIMAL(10,2) DEFAULT 0,
  total DECIMAL(10,2) DEFAULT 0,
  payment_method VARCHAR(50),
  status ENUM('confirmed','cancelled','refunded') DEFAULT 'confirmed',
  delivery_status ENUM('processing','shipped') DEFAULT 'processing',
  shipping_address TEXT,
  notes TEXT,
  vip_discount_applied TINYINT(1) DEFAULT 0,
  referral_from VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id),
  FOREIGN KEY (referral_from) REFERENCES clients(id) ON DELETE SET NULL
);

-- Order items table
CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(20) NOT NULL,
  product_id VARCHAR(20) NOT NULL,
  qty INT DEFAULT 1,
  unit_price DECIMAL(10,2) DEFAULT 0,
  line_total DECIMAL(10,2) DEFAULT 0,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2) DEFAULT 0,
  payment_date DATE,
  method VARCHAR(50),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Follow-ups table
CREATE TABLE IF NOT EXISTS follow_ups (
  id VARCHAR(20) PRIMARY KEY,
  order_id VARCHAR(20) NOT NULL,
  follow_date DATE NOT NULL,
  note TEXT,
  status ENUM('pending','done') DEFAULT 'pending',
  sent TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Payment methods table
CREATE TABLE IF NOT EXISTS payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

-- Activity log table
CREATE TABLE IF NOT EXISTS activity_log (
  id VARCHAR(20) PRIMARY KEY,
  action VARCHAR(30) NOT NULL,
  entity VARCHAR(30) NOT NULL,
  entity_id VARCHAR(20),
  extra TEXT,
  snapshot JSON,
  restorable TINYINT(1) DEFAULT 0,
  restored TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Stock movements table
CREATE TABLE IF NOT EXISTS stock_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id VARCHAR(20) NOT NULL,
  type ENUM('in','out','adjust') NOT NULL,
  qty INT NOT NULL,
  reason VARCHAR(100),
  reference_id VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Stock reduction reports table
CREATE TABLE IF NOT EXISTS stock_reduction_reports (
  id VARCHAR(20) PRIMARY KEY,
  product_id VARCHAR(20) NOT NULL,
  reported_by INT NOT NULL,
  reduction_type VARCHAR(60) NOT NULL,
  qty INT DEFAULT 1,
  description TEXT NOT NULL,
  photo_1 VARCHAR(500) NULL,
  photo_2 VARCHAR(500) NULL,
  status ENUM('pending','processed','resolved') DEFAULT 'pending',
  processed_by INT,
  processed_at TIMESTAMP NULL,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  archived_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (reported_by) REFERENCES users(id),
  FOREIGN KEY (processed_by) REFERENCES users(id)
);

-- Generic key-value settings (firewall password, feature flags, etc.)
CREATE TABLE IF NOT EXISTS app_settings (
  key_name VARCHAR(100) PRIMARY KEY,
  value TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
  id VARCHAR(20) PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  action VARCHAR(30) NOT NULL,
  title VARCHAR(200) NOT NULL,
  message TEXT,
  detail JSON,
  entity VARCHAR(30),
  entity_id VARCHAR(20),
  triggered_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (triggered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Per-user notification read/delete status
CREATE TABLE IF NOT EXISTS notification_reads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  notification_id VARCHAR(20) NOT NULL,
  user_id INT NOT NULL,
  is_read TINYINT(1) DEFAULT 0,
  read_at TIMESTAMP NULL,
  is_deleted TINYINT(1) DEFAULT 0,
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_notif_user (notification_id, user_id)
);

CREATE INDEX idx_notif_reads_user ON notification_reads(user_id, is_read, is_deleted);

-- Seed default master admin user (password: admin)
INSERT IGNORE INTO users (username, full_name, password_hash, role, status) VALUES
('admin', 'Administrator', '$2a$10$8Kx1DG1VQ5Y5J5j5Y5j5YeKx1DG1VQ5Y5J5j5Y5j5YeKx1DG1VQ5', 'master_admin', 'active');

-- Seed payment methods
INSERT IGNORE INTO payment_methods (name) VALUES
('Zelle'), ('Bank A'), ('Bank B'), ('Cash'), ('Venmo');

-- Seed products
INSERT IGNORE INTO products (id, name, description, mg, ml, route, cost_price, retail_price, stock_qty, batch, expiry, supplier, low_threshold) VALUES
('p1', 'Tirzepatide', 'GLP-1/GIP receptor agonist for weight management', 5, 2, 'SQ', 120, 250, 30, 'TZ-2026-A', '2026-12-01', 'PeptideCo', 5),
('p2', 'Semaglutide', 'GLP-1 agonist for appetite suppression', 2.4, 1.5, 'SQ', 95, 200, 8, 'SM-2026-B', '2026-08-15', 'BioSupply', 5),
('p3', 'BPC-157', 'Body protection compound for tissue repair', 5, 3, 'SQ', 45, 110, 50, 'BPC-2026-C', '2027-03-20', 'PeptideCo', 5),
('p4', 'CJC-1295/Ipamorelin', 'Growth hormone secretagogue blend', 10, 5, 'SQ', 80, 180, 22, 'CJC-2026-D', '2026-11-30', 'GrowthLab', 5),
('p5', 'PT-141', 'Melanocortin receptor agonist', 2, 1, 'SQ', 55, 130, 3, 'PT-2026-E', '2026-06-10', 'BioSupply', 5);

-- Seed clients
INSERT IGNORE INTO clients (id, name, phone, email, address, allergies, notes, is_deleted, is_vip, referred_by) VALUES
('c1', 'Sarah Mitchell', '310-555-0142', 'sarah.m@email.com', '1234 Sunset Blvd, Los Angeles, CA 90028', 'None', 'Tirzepatide cycle 8 weeks', 0, 1, NULL),
('c2', 'James Rodriguez', '713-555-0198', 'james.r@email.com', '5678 Westheimer Rd, Houston, TX 77056', 'Penicillin', 'BPC-157 protocol', 0, 0, 'c1'),
('c3', 'Emily Chen', '415-555-0267', 'emily.c@email.com', '900 Market St, San Francisco, CA 94102', 'None', 'New client', 0, 0, NULL);

-- Seed orders
INSERT IGNORE INTO orders (id, client_id, order_date, subtotal, discount_pct, discount_amt, total, payment_method, status, delivery_status, shipping_address, notes, vip_discount_applied, referral_from) VALUES
('O-001', 'c1', '2026-04-08', 500, 10, 50, 450, 'Zelle', 'confirmed', 'shipped', '1234 Sunset Blvd, Los Angeles, CA 90028', 'Deliver before 5pm', 1, NULL),
('O-002', 'c2', '2026-04-09', 290, 0, 0, 290, 'Bank A', 'confirmed', 'processing', '5678 Westheimer Rd, Houston, TX 77056', '', 0, 'c1');

-- Seed order items
INSERT IGNORE INTO order_items (order_id, product_id, qty, unit_price, line_total) VALUES
('O-001', 'p1', 2, 250, 500),
('O-002', 'p3', 1, 110, 110),
('O-002', 'p4', 1, 180, 180);

-- Seed payments
INSERT IGNORE INTO payments (order_id, amount, payment_date, method) VALUES
('O-001', 450, '2026-04-08', 'Zelle'),
('O-002', 290, '2026-04-09', 'Bank A');

-- Seed follow-ups
INSERT IGNORE INTO follow_ups (id, order_id, follow_date, note, status, sent) VALUES
('f1', 'O-001', '2026-04-22', 'Check progress & ask about refill', 'pending', 0);