-- Core tables
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(191) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','chair','pc','author','registrant') NOT NULL DEFAULT 'author',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conferences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(64) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
  start_date DATE NULL,
  end_date DATE NULL,
  theme VARCHAR(64) DEFAULT 'default',
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tracks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS submissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  track_id INT NULL,
  author_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  abstract TEXT,
  blind TINYINT(1) DEFAULT 1,
  status ENUM('submitted','under_review','rebuttal','accepted','rejected','camera_ready') DEFAULT 'submitted',
  version INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conference_id) REFERENCES conferences(id),
  FOREIGN KEY (track_id) REFERENCES tracks(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS submission_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  submission_id INT NOT NULL,
  stage ENUM('initial','rebuttal','camera_ready') NOT NULL,
  path VARCHAR(255) NOT NULL,
  mime VARCHAR(128) NULL,
  size INT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (submission_id) REFERENCES submissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reviewers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  conference_id INT NOT NULL,
  expertise TEXT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bids (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reviewer_id INT NOT NULL,
  submission_id INT NOT NULL,
  bid ENUM('eager','willing','neutral','unwilling','conflict') NOT NULL,
  FOREIGN KEY (reviewer_id) REFERENCES reviewers(id),
  FOREIGN KEY (submission_id) REFERENCES submissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conflicts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reviewer_id INT NOT NULL,
  author_id INT NOT NULL,
  type VARCHAR(64) NOT NULL,
  FOREIGN KEY (reviewer_id) REFERENCES reviewers(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  submission_id INT NOT NULL,
  reviewer_id INT NOT NULL,
  score DECIMAL(4,2) NULL,
  recommendation ENUM('accept','weak_accept','borderline','weak_reject','reject') NULL,
  comments_to_authors TEXT,
  comments_to_pc TEXT,
  is_anonymous TINYINT(1) DEFAULT 1,
  submitted_at TIMESTAMP NULL,
  FOREIGN KEY (submission_id) REFERENCES submissions(id),
  FOREIGN KEY (reviewer_id) REFERENCES reviewers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS decisions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  submission_id INT NOT NULL UNIQUE,
  decision ENUM('accept','reject','poster','oral') NULL,
  notes TEXT NULL,
  decided_at TIMESTAMP NULL,
  FOREIGN KEY (submission_id) REFERENCES submissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS registrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  user_id INT NOT NULL,
  ticket_type VARCHAR(64) NOT NULL,
  price_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  paid TINYINT(1) DEFAULT 0,
  invoice_no VARCHAR(32) UNIQUE,
  qr_code VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conference_id) REFERENCES conferences(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  location VARCHAR(255) NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NOT NULL,
  description TEXT NULL,
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS session_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id INT NOT NULL,
  submission_id INT NULL,
  title VARCHAR(255) NOT NULL,
  speaker VARCHAR(255) NULL,
  order_no INT NOT NULL DEFAULT 0,
  FOREIGN KEY (session_id) REFERENCES sessions(id),
  FOREIGN KEY (submission_id) REFERENCES submissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  slug VARCHAR(128) NOT NULL,
  title VARCHAR(255) NOT NULL,
  content MEDIUMTEXT NULL,
  is_public TINYINT(1) DEFAULT 1,
  UNIQUE KEY conf_slug (conference_id, slug),
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(64) NOT NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extended entities
CREATE TABLE IF NOT EXISTS conference_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  `key` VARCHAR(64) NOT NULL,
  `value` TEXT NULL,
  UNIQUE KEY conf_key (conference_id, `key`),
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS form_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  scope ENUM('submission','review','registration') NOT NULL,
  name VARCHAR(64) NOT NULL,
  label VARCHAR(255) NOT NULL,
  type ENUM('text','textarea','select','checkbox','radio','file','number') NOT NULL DEFAULT 'text',
  options TEXT NULL, -- JSON for select/radio
  required TINYINT(1) DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS form_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  field_id INT NOT NULL,
  entity_type ENUM('submission','review','registration') NOT NULL,
  entity_id INT NOT NULL,
  value TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (field_id) REFERENCES form_fields(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  name VARCHAR(128) NOT NULL,
  price_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  qty_total INT NULL,
  qty_sold INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  code VARCHAR(32) NOT NULL UNIQUE,
  type ENUM('percent','amount') NOT NULL DEFAULT 'percent',
  value INT NOT NULL DEFAULT 0, -- cents or percent
  active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  registration_id INT NOT NULL,
  total_cents INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL,
  FOREIGN KEY (registration_id) REFERENCES registrations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  provider ENUM('paypal','stripe') NOT NULL,
  provider_ref VARCHAR(128) NULL,
  amount_cents INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('succeeded','failed','pending') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conference_id INT NOT NULL,
  slug VARCHAR(64) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  UNIQUE KEY conf_slug (conference_id, slug),
  FOREIGN KEY (conference_id) REFERENCES conferences(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
