const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const bcrypt = require('bcryptjs');

const dbPath = path.resolve(__dirname, 'database.sqlite');
const db = new sqlite3.Database(dbPath);

const initDb = () => {
  db.serialize(() => {
    // Users Table
    db.run(`CREATE TABLE IF NOT EXISTS "users" (
        "id" INTEGER,
        "email" TEXT NOT NULL UNIQUE,
        "password" TEXT NOT NULL,
        "firstName" TEXT NOT NULL,
        "lastName" TEXT NOT NULL,
        "role" TEXT DEFAULT 'admin',
        "membershipStatus" TEXT DEFAULT 'inactive',
        "membershipStartDate" DATETIME,
        "membershipEndDate" DATETIME,
        "company" TEXT,
        "profileImage" TEXT,
        "isActive" INTEGER DEFAULT 1,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "updatedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT)
    )`);

    // Add reset token columns if they don't exist
    db.run(`ALTER TABLE users ADD COLUMN resetToken TEXT`, (err) => { /* ignore if exists */ });
    db.run(`ALTER TABLE users ADD COLUMN resetTokenExpires DATETIME`, (err) => { /* ignore if exists */ });

    // Documents Table
    db.run(`CREATE TABLE IF NOT EXISTS "documents" (
        "id" INTEGER,
        "title" TEXT NOT NULL,
        "description" TEXT,
        "content" TEXT NOT NULL,
        "createdBy" INTEGER NOT NULL,
        "allowDownload" INTEGER DEFAULT 1,
        "fileFormat" TEXT DEFAULT 'pdf',
        "status" TEXT DEFAULT 'draft',
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "updatedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("createdBy") REFERENCES "users"("id")
    )`);

    // Membership Table
    db.run(`CREATE TABLE IF NOT EXISTS "membership" (
        "id" INTEGER,
        "userId" INTEGER NOT NULL UNIQUE,
        "planType" TEXT DEFAULT 'basic',
        "status" TEXT DEFAULT 'active',
        "startDate" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "endDate" DATETIME,
        "autoRenew" INTEGER DEFAULT 1,
        "price" REAL,
        "currency" TEXT DEFAULT 'USD',
        "paymentMethod" TEXT,
        "stripeCustomerId" TEXT,
        "stripeSubscriptionId" TEXT,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "updatedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("userId") REFERENCES "users"("id")
    )`);

    // Signature Fields Table
    db.run(`CREATE TABLE IF NOT EXISTS "signatureFields" (
        "id" INTEGER,
        "documentId" INTEGER NOT NULL,
        "fieldId" TEXT NOT NULL,
        "type" TEXT NOT NULL,
        "coordinateX" REAL,
        "coordinateY" REAL,
        "page" INTEGER,
        "width" REAL,
        "height" REAL,
        "required" INTEGER DEFAULT 1,
        "assignedTo" TEXT,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("documentId") REFERENCES "documents"("id")
    )`);

    // Signatures Table
    db.run(`CREATE TABLE IF NOT EXISTS "signatures" (
        "id" INTEGER,
        "documentId" INTEGER NOT NULL,
        "signerEmail" TEXT NOT NULL,
        "signerName" TEXT,
        "signatureImage" TEXT NOT NULL,
        "fieldId" TEXT,
        "ipAddress" TEXT,
        "userAgent" TEXT,
        "signedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "documentHash" TEXT,
        "isInitial" INTEGER DEFAULT 0,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        "updatedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("documentId") REFERENCES "documents"("id")
    )`);

    // Signers Table
    db.run(`CREATE TABLE IF NOT EXISTS "signers" (
        "id" INTEGER,
        "documentId" INTEGER NOT NULL,
        "email" TEXT NOT NULL,
        "name" TEXT,
        "status" TEXT DEFAULT 'pending',
        "signerToken" TEXT,
        "signedDate" DATETIME,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("documentId") REFERENCES "documents"("id")
    )`);

    // Analytics Table
    db.run(`CREATE TABLE IF NOT EXISTS "analytics" (
        "id" INTEGER,
        "ipAddress" TEXT,
        "country" TEXT,
        "city" TEXT,
        "device" TEXT,
        "browser" TEXT,
        "path" TEXT,
        "documentId" INTEGER,
        "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT)
    )`);

    // Action Logs Table
    db.run(`CREATE TABLE IF NOT EXISTS "action_logs" (
        "id" INTEGER,
        "userId" INTEGER NOT NULL,
        "actionType" TEXT NOT NULL,
        "description" TEXT,
        "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY("id" AUTOINCREMENT),
        FOREIGN KEY("userId") REFERENCES "users"("id")
    )`);
    // Plans Table
    db.run(`CREATE TABLE IF NOT EXISTS "plans" (
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "name" TEXT NOT NULL,
        "subtitle" TEXT NOT NULL,
        "price" REAL NOT NULL,
        "features" TEXT NOT NULL,
        "isPopular" INTEGER DEFAULT 0
    )`, (err) => {
        if (!err) {
            db.get("SELECT COUNT(*) as count FROM plans", (err, row) => {
                if (row && row.count === 0) {
                    const basicFeatures = JSON.stringify(['Up to 5 documents', 'Up to 30 signers per document', 'Standard support']);
                    const proFeatures = JSON.stringify(['Up to 10 documents', 'Up to 70 signers per document', 'Priority support', 'Advanced analytics']);
                    const entFeatures = JSON.stringify(['Unlimited documents', 'Unlimited signers', 'Dedicated support', 'White-labeling']);
                    
                    db.run(`INSERT INTO plans (name, subtitle, price, features, isPopular) VALUES ('Basic', 'Perfect for getting started', 80, ?, 0)`, [basicFeatures]);
                    db.run(`INSERT INTO plans (name, subtitle, price, features, isPopular) VALUES ('Small Business', 'For growing businesses', 150, ?, 1)`, [proFeatures]);
                    db.run(`INSERT INTO plans (name, subtitle, price, features, isPopular) VALUES ('Enterprise', 'For large organizations', 250, ?, 0)`, [entFeatures]);
                }
            });
        }
    });

    // Seed Superadmin
    const salt = bcrypt.genSaltSync(10);
    const hash = bcrypt.hashSync('password123', salt);
    db.run(`INSERT OR IGNORE INTO users (id, email, password, firstName, lastName, role, membershipStatus) 
            VALUES (1, 'superadmin@makeitservices.org', ?, 'Super', 'Admin', 'superadmin', 'active')`, [hash]);
    
    // Add new advanced workflow columns (will quietly fail if they already exist, which is fine)
    db.run(`ALTER TABLE documents ADD COLUMN workflowType TEXT DEFAULT 'none'`, (err) => {});
    db.run(`ALTER TABLE documents ADD COLUMN adminSignature TEXT`, (err) => {});
    db.run(`ALTER TABLE documents ADD COLUMN adminSignedAt DATETIME`, (err) => {});
    db.run(`ALTER TABLE signers ADD COLUMN expiresAt DATETIME`, (err) => {});
    db.run(`ALTER TABLE users ADD COLUMN isActive INTEGER DEFAULT 1`, (err) => {});

    console.log('Database initialized successfully');
  });
};

const logAction = (userId, actionType, description) => {
    db.run('INSERT INTO action_logs (userId, actionType, description) VALUES (?, ?, ?)', [userId, actionType, description], (err) => {
        if (err) console.error("Action log error:", err);
    });
};

module.exports = {
  db,
  initDb,
  logAction
};
