-- =============================================
-- BackFeed Database Schema
-- =============================================

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

-- Users / Admins
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','member') DEFAULT 'member',
    permissions JSON NULL COMMENT 'module access: dashboard,projects,forms,responses,analytics,automation,email_queue,integration,settings,users',
    is_active TINYINT(1) DEFAULT 1,
    must_change_password TINYINT(1) DEFAULT 0,
    last_login DATETIME NULL,
    invited_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Projects / Workspaces
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    domain VARCHAR(255) NULL,
    project_key VARCHAR(64) NOT NULL UNIQUE,
    description TEXT NULL,
    logo VARCHAR(255) NULL,
    brand_color VARCHAR(20) DEFAULT '#0f2557',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Forms
CREATE TABLE IF NOT EXISTS forms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    trigger_type ENUM('page_load','action','time_delay','exit_intent','scroll_depth','manual') DEFAULT 'manual',
    trigger_delay INT DEFAULT 0 COMMENT 'seconds',
    trigger_scroll INT DEFAULT 50 COMMENT 'percent',
    display_type ENUM('popup','slide_in','embedded') DEFAULT 'popup',
    frequency ENUM('once','every_visit','custom') DEFAULT 'once',
    target_urls TEXT NULL COMMENT 'JSON array of URL patterns',
    logo VARCHAR(255) NULL,
    brand_color VARCHAR(20) DEFAULT '#0f2557',
    accent_color VARCHAR(20) DEFAULT '#f97316',
    font_family VARCHAR(100) DEFAULT 'Inter',
    thank_you_title VARCHAR(255) DEFAULT 'Thank you for your feedback!',
    thank_you_message TEXT DEFAULT 'We appreciate you taking the time to share your thoughts.',
    status ENUM('active','inactive','draft') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Form Fields
CREATE TABLE IF NOT EXISTS form_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    form_id INT NOT NULL,
    field_key VARCHAR(100) NOT NULL,
    label VARCHAR(255) NOT NULL,
    field_type ENUM('short_text','long_text','multiple_choice','checkbox','rating_stars','rating_nps','dropdown','email','phone') NOT NULL,
    placeholder VARCHAR(255) NULL,
    options TEXT NULL COMMENT 'JSON array for choice/dropdown fields',
    is_required TINYINT(1) DEFAULT 0,
    is_rating_field TINYINT(1) DEFAULT 0 COMMENT 'used for automation',
    sort_order INT DEFAULT 0,
    conditional_logic TEXT NULL COMMENT 'JSON rules',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES forms(id) ON DELETE CASCADE
);

-- Automation Configuration
CREATE TABLE IF NOT EXISTS automation_configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    form_id INT NOT NULL UNIQUE,
    is_enabled TINYINT(1) DEFAULT 0,
    rating_field_id INT NULL,
    poor_threshold DECIMAL(4,1) DEFAULT 2.0,
    good_threshold DECIMAL(4,1) DEFAULT 4.0,
    -- Poor email
    poor_email_subject VARCHAR(255) DEFAULT 'We''re sorry to hear that...',
    poor_email_body TEXT NULL,
    -- Good email
    good_email_subject VARCHAR(255) DEFAULT 'Thank you for the great feedback!',
    good_email_body TEXT NULL,
    good_email_cta_label VARCHAR(100) DEFAULT 'Leave a Review',
    good_email_cta_url VARCHAR(500) NULL,
    -- Sender config
    from_name VARCHAR(100) NULL,
    from_email VARCHAR(150) NULL,
    reply_to VARCHAR(150) NULL,
    -- Delay
    delay_type ENUM('instant','scheduled') DEFAULT 'instant',
    delay_minutes INT DEFAULT 0,
    -- Score mapping for non-numeric trigger fields (multiple_choice/dropdown),
    -- e.g. {"Very Satisfied": 5, "Satisfied": 4, "Neutral": 3, "Unsatisfied": 1}
    option_score_map TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES forms(id) ON DELETE CASCADE,
    FOREIGN KEY (rating_field_id) REFERENCES form_fields(id) ON DELETE SET NULL
);

-- Feedback Responses
CREATE TABLE IF NOT EXISTS responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    form_id INT NOT NULL,
    session_id VARCHAR(128) NULL,
    user_id_external VARCHAR(255) NULL COMMENT 'passed from website',
    action_id VARCHAR(255) NULL,
    event_name VARCHAR(100) NULL,
    -- Metadata
    page_url TEXT NULL,
    page_title VARCHAR(500) NULL,
    referrer_url TEXT NULL,
    device_type ENUM('desktop','tablet','mobile','unknown') DEFAULT 'unknown',
    browser VARCHAR(100) NULL,
    os VARCHAR(100) NULL,
    ip_address VARCHAR(45) NULL,
    geo_country VARCHAR(100) NULL,
    geo_city VARCHAR(100) NULL,
    respondent_email VARCHAR(150) NULL,
    -- Email automation
    email_sent TINYINT(1) DEFAULT 0,
    email_type ENUM('good','poor') NULL,
    email_status ENUM('sent','failed','pending','none') DEFAULT 'none',
    email_sent_at DATETIME NULL,
    email_template_id INT NULL,
    -- Timestamps
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES forms(id) ON DELETE CASCADE
);

-- Response Field Values
CREATE TABLE IF NOT EXISTS response_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    field_id INT NOT NULL,
    field_key VARCHAR(100) NOT NULL,
    field_label VARCHAR(255) NOT NULL,
    value TEXT NULL,
    FOREIGN KEY (response_id) REFERENCES responses(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES form_fields(id) ON DELETE CASCADE
);

-- Email Queue
CREATE TABLE IF NOT EXISTS email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    to_email VARCHAR(150) NOT NULL,
    to_name VARCHAR(100) NULL,
    from_name VARCHAR(100) NOT NULL,
    from_email VARCHAR(150) NOT NULL,
    reply_to VARCHAR(150) NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    email_type ENUM('good','poor') NOT NULL,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    scheduled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (response_id) REFERENCES responses(id) ON DELETE CASCADE
);

-- Sessions / Auth Tokens
CREATE TABLE IF NOT EXISTS admin_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(128) NOT NULL UNIQUE,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SMTP Settings per project
CREATE TABLE IF NOT EXISTS smtp_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    provider ENUM('sendgrid','smtp','mailgun','ses') DEFAULT 'sendgrid',
    sendgrid_api_key VARCHAR(255) NULL,
    host VARCHAR(255) NULL,
    port INT DEFAULT 587,
    username VARCHAR(255) NULL,
    password VARCHAR(255) NULL,
    encryption ENUM('tls','ssl','none') DEFAULT 'tls',
    from_name VARCHAR(100) DEFAULT 'BackFeed',
    from_email VARCHAR(150) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX idx_responses_form ON responses(form_id);
CREATE INDEX idx_responses_submitted ON responses(submitted_at);
CREATE INDEX idx_email_queue_status ON email_queue(status);
CREATE INDEX idx_form_fields_form ON form_fields(form_id);
CREATE INDEX idx_projects_key ON projects(project_key);

-- =============================================
-- Default Admin User (change password after setup)
-- Password: Admin@1234
-- =============================================
INSERT INTO users (name, email, password_hash, role) VALUES
('BackFeed Admin', 'admin@backfeed.app', '$2y$10$MJxvMF1PHR70QoUr2qvd7ugk23CSnJSWVei2GvHz7chR76j3q4U7i', 'admin');