-- IPTRM MySQL schema (utf8mb4). Run on your server's MySQL.
-- The app auto-creates these via SQLAlchemy, but this is provided for reference/manual setup.
CREATE DATABASE IF NOT EXISTS iptrm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE iptrm;

CREATE TABLE IF NOT EXISTS issues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  publication_date DATE NULL,
  title VARCHAR(255),
  expected_count INT DEFAULT 0,
  status ENUM('open','complete','sent') NOT NULL DEFAULT 'open',
  created_at DATETIME, sent_at DATETIME,
  INDEX(publication_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  issue_id INT NOT NULL,
  adv_number VARCHAR(64), adv_date DATE, adv_date_raw VARCHAR(32),
  app_number VARCHAR(64), app_date DATE, app_date_raw VARCHAR(32),
  mark_text TEXT, mark_text_en VARCHAR(512), mark_translation VARCHAR(512),
  owner VARCHAR(512), owner_en VARCHAR(512),
  owner_entity VARCHAR(255), owner_reg_no VARCHAR(64),
  nationality VARCHAR(128), nationality_en VARCHAR(128),
  applicant_type VARCHAR(16),
  applicant_entity_en VARCHAR(255), legal_rep VARCHAR(512), address TEXT,
  nice_class VARCHAR(128),
  goods_services TEXT, goods_services_en TEXT,
  disclaimer TEXT, disclaimer_en TEXT,
  mark_image_path VARCHAR(512),
  source_pdf_path VARCHAR(512), source_pdf_name VARCHAR(255),
  verified BOOLEAN DEFAULT 0, extraction_ok BOOLEAN DEFAULT 1,
  created_at DATETIME,
  INDEX(issue_id), INDEX(app_number), INDEX(adv_number),
  FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  company VARCHAR(255),
  active BOOLEAN DEFAULT 1,
  pref_format VARCHAR(16) DEFAULT 'pdf',
  pref_design VARCHAR(32) DEFAULT 'minimal',
  pref_language VARCHAR(16) DEFAULT 'en',
  classes_of_interest VARCHAR(255),
  monthly_fee DECIMAL(10,2) DEFAULT 0,
  created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subscriber_id INT NOT NULL,
  number VARCHAR(32) UNIQUE,
  period_start DATE, period_end DATE,
  amount DECIMAL(10,2) DEFAULT 0,
  issued_at DATETIME, paid BOOLEAN DEFAULT 0,
  FOREIGN KEY (subscriber_id) REFERENCES subscribers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  issue_id INT, subscriber_id INT,
  to_email VARCHAR(255), subject VARCHAR(512),
  fmt VARCHAR(16), status VARCHAR(32), error TEXT,
  sent_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transliterations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  value VARCHAR(255) NOT NULL,
  kind VARCHAR(16) DEFAULT 'auto',
  is_primary BOOLEAN DEFAULT 0,
  created_at DATETIME,
  INDEX(application_id), INDEX(value),
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(16) DEFAULT 'admin',
  active BOOLEAN DEFAULT 1,
  created_at DATETIME, last_login DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS field_translations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  lang VARCHAR(8) NOT NULL,
  field VARCHAR(32) NOT NULL,
  value TEXT,
  created_at DATETIME,
  INDEX(application_id), INDEX(lang),
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS watched_marks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_name VARCHAR(255), label VARCHAR(255) NOT NULL,
  farsi VARCHAR(255), classes VARCHAR(255),
  subscriber_id INT NULL, active BOOLEAN DEFAULT 1, created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conflict_alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  watched_id INT NOT NULL, application_id INT NOT NULL,
  score DECIMAL(5,2), kind VARCHAR(32), matched_on VARCHAR(255),
  class_overlap BOOLEAN DEFAULT 0, status VARCHAR(16) DEFAULT 'open',
  created_at DATETIME,
  INDEX(watched_id), INDEX(application_id),
  FOREIGN KEY (watched_id) REFERENCES watched_marks(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;