CREATE DATABASE IF NOT EXISTS `domain_hosting_manager` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `domain_hosting_manager`;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(255) NOT NULL,
  company VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(100),
  notes TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hosting_services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  label VARCHAR(255) NOT NULL,
  provider VARCHAR(255),
  plan VARCHAR(255),
  panel_url VARCHAR(512),
  ip_server VARCHAR(255),
  start_date DATE,
  renewal_date DATE,
  billing_cycle ENUM('monthly','yearly') NOT NULL DEFAULT 'monthly',
  price DECIMAL(10,2),
  status ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active',
  notes TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX (client_id),
  CONSTRAINT fk_hosting_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  domain_name VARCHAR(255) NOT NULL UNIQUE,
  registrar VARCHAR(255),
  login_url VARCHAR(512),
  purchase_date DATE,
  expiration_date DATE,
  auto_renew TINYINT(1) NOT NULL DEFAULT 0,
  annual_price DECIMAL(10,2),
  status ENUM('active','expired','transferred') NOT NULL DEFAULT 'active',
  hosting_id INT NULL,
  notes TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX (client_id),
  INDEX (hosting_id),
  CONSTRAINT fk_domain_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_domain_hosting FOREIGN KEY (hosting_id) REFERENCES hosting_services(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entity_type ENUM('domain','hosting') NOT NULL,
  entity_id INT NOT NULL,
  client_id INT NOT NULL,
  alert_day_offset INT NOT NULL,
  scheduled_date DATE,
  channel ENUM('email') NOT NULL DEFAULT 'email',
  status ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
  sent_at DATETIME NULL,
  error_message TEXT,
  created_at DATETIME NOT NULL,
  INDEX (client_id),
  INDEX (entity_id),
  UNIQUE KEY uniq_alert (entity_type, entity_id, alert_day_offset, scheduled_date),
  CONSTRAINT fk_alert_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  note TEXT,
  created_at DATETIME NOT NULL,
  INDEX (client_id),
  CONSTRAINT fk_payment_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(64) NOT NULL,
  success TINYINT(1) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX (ip),
  INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS registrars (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS providers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE domains ADD COLUMN registrar_id INT NULL, ADD INDEX (registrar_id);
ALTER TABLE hosting_services ADD COLUMN provider_id INT NULL, ADD INDEX (provider_id);
ALTER TABLE hosting_services ADD CONSTRAINT fk_hosting_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL;
ALTER TABLE domains ADD CONSTRAINT fk_domain_registrar FOREIGN KEY (registrar_id) REFERENCES registrars(id) ON DELETE SET NULL;

INSERT IGNORE INTO registrars(name) SELECT DISTINCT registrar FROM domains WHERE registrar IS NOT NULL AND registrar <> '';
INSERT IGNORE INTO providers(name) SELECT DISTINCT provider FROM hosting_services WHERE provider IS NOT NULL AND provider <> '';
UPDATE domains d JOIN registrars r ON r.name = d.registrar SET d.registrar_id = r.id WHERE d.registrar_id IS NULL;
UPDATE hosting_services h JOIN providers p ON p.name = h.provider SET h.provider_id = p.id WHERE h.provider_id IS NULL;
ALTER TABLE alerts MODIFY COLUMN status ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending';

ALTER TABLE hosting_services ADD COLUMN primary_domain_id INT NULL;
ALTER TABLE hosting_services ADD COLUMN renewal_mode ENUM('SYNC_WITH_DOMAIN','CUSTOM_DATE') NOT NULL DEFAULT 'CUSTOM_DATE';
ALTER TABLE hosting_services ADD COLUMN ssl_provider ENUM('NONE','INTERNAL','CLOUDFLARE') NOT NULL DEFAULT 'NONE';
ALTER TABLE hosting_services ADD COLUMN ssl_status ENUM('PENDING','ACTIVE','EXPIRED') NOT NULL DEFAULT 'PENDING';
ALTER TABLE hosting_services ADD COLUMN ssl_expiration_date DATE NULL;
ALTER TABLE hosting_services ADD COLUMN ssl_auto_renew TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE hosting_services ADD COLUMN cloudflare_zone VARCHAR(255) NULL;
ALTER TABLE hosting_services ADD COLUMN cloudflare_notes TEXT NULL;
ALTER TABLE hosting_services ADD CONSTRAINT fk_hosting_primary_domain FOREIGN KEY (primary_domain_id) REFERENCES domains(id) ON DELETE SET NULL;

UPDATE hosting_services h JOIN domains d ON d.hosting_id = h.id SET h.primary_domain_id = d.id WHERE h.primary_domain_id IS NULL;

ALTER TABLE alerts ADD COLUMN alert_category ENUM('RENEWAL','SSL') NOT NULL DEFAULT 'RENEWAL';
ALTER TABLE alerts DROP INDEX uniq_alert;
ALTER TABLE alerts ADD UNIQUE KEY uniq_alert (entity_type, entity_id, alert_category, alert_day_offset, scheduled_date);

ALTER TABLE domains
  ADD COLUMN site_url VARCHAR(255) NULL,
  ADD COLUMN site_status ENUM('UNKNOWN','ONLINE','WARNING','DOWN') NOT NULL DEFAULT 'UNKNOWN',
  ADD COLUMN last_site_check_at DATETIME NULL,
  ADD COLUMN site_http_code INT NULL,
  ADD COLUMN site_response_time_ms INT NULL,
  ADD COLUMN site_error_message TEXT NULL;

CREATE TABLE IF NOT EXISTS site_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  domain_id INT NOT NULL,
  checked_at DATETIME NOT NULL,
  url_tested VARCHAR(255) NOT NULL,
  http_code INT NULL,
  response_time_ms INT NULL,
  status ENUM('ONLINE','WARNING','DOWN') NOT NULL,
  error_message TEXT NULL,
  INDEX idx_site_checks_domain_checked (domain_id, checked_at),
  CONSTRAINT fk_site_checks_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_domains_site_status ON domains(site_status);

ALTER TABLE domains
  ADD COLUMN ssl_host VARCHAR(255) NULL,
  ADD COLUMN ssl_port INT NOT NULL DEFAULT 443,
  ADD COLUMN ssl_status ENUM('UNKNOWN','NONE','VALID','EXPIRING','EXPIRED','ERROR') NOT NULL DEFAULT 'UNKNOWN',
  ADD COLUMN ssl_expiration_date DATE NULL,
  ADD COLUMN ssl_days_remaining INT NULL,
  ADD COLUMN ssl_issuer VARCHAR(255) NULL,
  ADD COLUMN ssl_subject VARCHAR(255) NULL,
  ADD COLUMN ssl_last_checked_at DATETIME NULL,
  ADD COLUMN ssl_error_message TEXT NULL;

CREATE TABLE IF NOT EXISTS ssl_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  domain_id INT NOT NULL,
  checked_at DATETIME NOT NULL,
  host VARCHAR(255) NOT NULL,
  port INT NOT NULL,
  status ENUM('NONE','VALID','EXPIRING','EXPIRED','ERROR') NOT NULL,
  expiration_date DATE NULL,
  issuer VARCHAR(255) NULL,
  subject VARCHAR(255) NULL,
  error_message TEXT NULL,
  INDEX idx_ssl_checks_domain_checked (domain_id, checked_at),
  CONSTRAINT fk_ssl_checks_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_domains_ssl_status ON domains(ssl_status);

CREATE TABLE IF NOT EXISTS packs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  domain_id INT NULL,
  hosting_id INT NULL,
  price_total DECIMAL(10,2),
  renewal_date DATE,
  status ENUM('active','expired') NOT NULL DEFAULT 'active',
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (client_id),
  INDEX (domain_id),
  INDEX (hosting_id),
  CONSTRAINT fk_pack_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_pack_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL,
  CONSTRAINT fk_pack_hosting FOREIGN KEY (hosting_id) REFERENCES hosting_services(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
