PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS supports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  support_id INTEGER NOT NULL,
  website TEXT NOT NULL CHECK (website IN ('king','trex','xtream')),
  country TEXT NOT NULL,
  creation_date TEXT NOT NULL,
  panel_value TEXT NOT NULL CHECK (
    panel_value IN ('medamirpro','strongpro','srongpro2','strongpro2','tivione','eagle4k')
  ),
  status TEXT NOT NULL CHECK (status IN ('active','expired')) DEFAULT 'active',
  type TEXT NOT NULL CHECK (type IN ('test','subscription')),
  subscription_period TEXT CHECK (
    subscription_period IS NULL OR
    subscription_period IN ('1_month','3_months','6_months','1_year')
  ),
  expiration_date TEXT NOT NULL,
  reminder_sent INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (support_id) REFERENCES supports(id)
);

CREATE TABLE IF NOT EXISTS email_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL,
  email_type TEXT NOT NULL,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  status TEXT NOT NULL,
  error_message TEXT,
  sent_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id)
);

INSERT OR IGNORE INTO supports (name, is_active) VALUES ('yasmin', 1);
INSERT OR IGNORE INTO supports (name, is_active) VALUES ('hafsa', 1);
