-- =========================================================
-- IMOSAI / AI FUNNEL - MySQL Schema (core)
-- Charset: utf8mb4
-- =========================================================

CREATE DATABASE IF NOT EXISTS imosai
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE imosai;

-- ----------------------------
-- USERS
-- ----------------------------
CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(120) NULL,
  role ENUM('owner','admin','editor','viewer') NOT NULL DEFAULT 'owner',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_users_email (email)
) ENGINE=InnoDB;

-- ----------------------------
-- PROJECTS (container of everything)
-- ----------------------------
CREATE TABLE projects (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  niche VARCHAR(190) NULL,
  language VARCHAR(10) NOT NULL DEFAULT 'bg',
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_projects_user (user_id),
  CONSTRAINT fk_projects_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------
-- IDEAS
-- ----------------------------
CREATE TABLE ideas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  problem TEXT NULL,
  audience TEXT NULL,
  notes LONGTEXT NULL,
  source ENUM('manual','ai') NOT NULL DEFAULT 'manual',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ideas_project (project_id),
  CONSTRAINT fk_ideas_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------
-- PRODUCTS (derived from Idea)
-- ----------------------------
CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  idea_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  product_type ENUM('digital','service','subscription','other') NOT NULL DEFAULT 'digital',
  description LONGTEXT NULL,
  deliverables LONGTEXT NULL,
  price_model ENUM('one_time','subscription','custom') NOT NULL DEFAULT 'one_time',
  base_price DECIMAL(10,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_products_project (project_id),
  KEY idx_products_idea (idea_id),
  CONSTRAINT fk_products_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_products_idea
    FOREIGN KEY (idea_id) REFERENCES ideas(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- OFFERS (derived from Product)
-- ----------------------------
CREATE TABLE offers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  positioning LONGTEXT NULL,
  value_props LONGTEXT NULL,
  objections LONGTEXT NULL,
  guarantee_text TEXT NULL,
  cta_text VARCHAR(190) NULL,
  pricing_text LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_offers_project (project_id),
  KEY idx_offers_product (product_id),
  CONSTRAINT fk_offers_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_offers_product
    FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- CAMPAIGNS (derived from Offer)
-- ----------------------------
CREATE TABLE campaigns (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  offer_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  channels JSON NULL,               -- e.g. ["facebook","olx","email"]
  angle VARCHAR(190) NULL,
  content_plan LONGTEXT NULL,       -- calendar / plan
  ad_copies LONGTEXT NULL,
  dm_scripts LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_campaigns_project (project_id),
  KEY idx_campaigns_offer (offer_id),
  CONSTRAINT fk_campaigns_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_campaigns_offer
    FOREIGN KEY (offer_id) REFERENCES offers(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- FUNNELS (derived from Campaign)
-- ----------------------------
CREATE TABLE funnels (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  funnel_type ENUM('simple','leadmagnet','webinar','application','custom') NOT NULL DEFAULT 'simple',
  steps LONGTEXT NULL,              -- step-by-step funnel logic
  landing_copy LONGTEXT NULL,
  checkout_copy LONGTEXT NULL,
  email_sequence LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_funnels_project (project_id),
  KEY idx_funnels_campaign (campaign_id),
  CONSTRAINT fk_funnels_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_funnels_campaign
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- PROMPTS (prompt templates per module)
-- ----------------------------
CREATE TABLE prompts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NULL,  -- NULL = global default prompt
  module ENUM('idea','product','offer','campaign','funnel') NOT NULL,
  name VARCHAR(190) NOT NULL,
  template LONGTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_prompts_project (project_id),
  KEY idx_prompts_module (module),
  CONSTRAINT fk_prompts_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------
-- AI RUNS (history of generations)
-- ----------------------------
CREATE TABLE ai_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  module ENUM('idea','product','offer','campaign','funnel') NOT NULL,
  entity_id BIGINT UNSIGNED NULL,       -- the created/updated entity id
  prompt_id BIGINT UNSIGNED NULL,
  input_json JSON NULL,
  output_text LONGTEXT NULL,
  status ENUM('queued','success','error') NOT NULL DEFAULT 'success',
  error_text TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_airuns_project (project_id),
  KEY idx_airuns_module (module),
  KEY idx_airuns_prompt (prompt_id),
  CONSTRAINT fk_airuns_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_airuns_prompt
    FOREIGN KEY (prompt_id) REFERENCES prompts(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- AUDIT LOGS
-- ----------------------------
CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,        -- e.g. "create_idea", "update_offer"
  entity_type VARCHAR(50) NULL,        -- "idea/product/offer/..."
  entity_id BIGINT UNSIGNED NULL,
  meta JSON NULL,
  ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_user (user_id),
  KEY idx_audit_project (project_id),
  CONSTRAINT fk_audit_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_audit_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;