import { MigrationInterface, QueryRunner } from 'typeorm';

export class CompleteSchemaMigration1700000000000 implements MigrationInterface {
  name = 'CompleteSchemaMigration1700000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Enable UUID extension
    await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

    // Roles table
    await queryRunner.query(`
      CREATE TABLE "roles" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar UNIQUE NOT NULL,
        "description" varchar NOT NULL,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Permissions table
    await queryRunner.query(`
      CREATE TABLE "permissions" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar UNIQUE NOT NULL,
        "description" varchar NOT NULL,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Role-Permission junction table
    await queryRunner.query(`
      CREATE TABLE "role_permissions" (
        "roleId" uuid REFERENCES "roles"("id") ON DELETE CASCADE,
        "permissionId" uuid REFERENCES "permissions"("id") ON DELETE CASCADE,
        PRIMARY KEY ("roleId", "permissionId")
      )
    `);

    // Users table (updated with roleId)
    await queryRunner.query(`
      CREATE TABLE "users" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "email" varchar UNIQUE NOT NULL,
        "passwordHash" varchar NOT NULL,
        "firstName" varchar NOT NULL,
        "lastName" varchar NOT NULL,
        "name" varchar NOT NULL,
        "roleId" uuid REFERENCES "roles"("id"),
        "isActive" boolean DEFAULT true,
        "resetToken" varchar,
        "resetTokenExpiration" timestamp with time zone,
        "company" varchar,
        "phone" varchar,
        "address" varchar,
        "profilePicture" varchar,
        "lastLogin" timestamp with time zone,
        "failedLoginAttempts" integer DEFAULT 0,
        "lockoutUntil" timestamp with time zone,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Customers table
    await queryRunner.query(`
      CREATE TABLE "customers" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "email" varchar UNIQUE NOT NULL,
        "phone" varchar NOT NULL,
        "address" text,
        "company" varchar,
        "notes" text,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Products table
    await queryRunner.query(`
      CREATE TABLE "products" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "description" text,
        "unit" varchar NOT NULL DEFAULT 'unit',
        "standardCost" numeric(10,2) NOT NULL,
        "sellingPrice" numeric(10,2) NOT NULL,
        "category" varchar,
        "image" varchar,
        "isPublic" boolean DEFAULT false,
        "isFeatured" boolean DEFAULT false,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Orders table
    await queryRunner.query(`
      CREATE TABLE "orders" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "customerId" uuid REFERENCES "customers"("id") ON DELETE CASCADE,
        "orderNumber" varchar UNIQUE NOT NULL,
        "status" varchar NOT NULL DEFAULT 'pending',
        "subtotal" numeric(10,2) NOT NULL,
        "tax" numeric(10,2) NOT NULL,
        "isTest" boolean DEFAULT false,
        "paymentStatus" varchar NOT NULL DEFAULT 'pending',
        "deliveryDate" timestamp with time zone,
        "specialRequests" jsonb DEFAULT '[]',
        "shippingAddress" jsonb,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Order Items table
    await queryRunner.query(`
      CREATE TABLE "order_items" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "orderId" uuid REFERENCES "orders"("id") ON DELETE CASCADE,
        "productId" uuid REFERENCES "products"("id") ON DELETE CASCADE,
        "quantity" integer NOT NULL,
        "unitPrice" numeric(10,2) NOT NULL,
        "specifications" jsonb,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Production Lines table
    await queryRunner.query(`
      CREATE TABLE "production_lines" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "type" varchar NOT NULL,
        "capacity" integer NOT NULL,
        "status" varchar NOT NULL DEFAULT 'active',
        "managerId" uuid REFERENCES "users"("id"),
        "specifications" jsonb,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Machines table
    await queryRunner.query(`
      CREATE TABLE "machines" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "productionLineId" uuid REFERENCES "production_lines"("id") ON DELETE CASCADE,
        "name" varchar NOT NULL,
        "model" varchar,
        "status" varchar NOT NULL DEFAULT 'active',
        "capacity" integer NOT NULL,
        "specifications" jsonb,
        "maintenanceHistory" jsonb DEFAULT '[]',
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Production Queue table
    await queryRunner.query(`
      CREATE TABLE "production_queue" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "orderId" uuid REFERENCES "orders"("id") ON DELETE CASCADE,
        "productionLineId" uuid REFERENCES "production_lines"("id") ON DELETE CASCADE,
        "status" varchar NOT NULL DEFAULT 'pending',
        "priority" integer NOT NULL,
        "estimatedCompletionTime" timestamp with time zone,
        "materials" text[],
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Suppliers table
    await queryRunner.query(`
      CREATE TABLE "suppliers" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "email" varchar,
        "phone" varchar,
        "address" text,
        "contactPerson" varchar,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Materials table
    await queryRunner.query(`
      CREATE TABLE "materials" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "description" text,
        "unit" varchar NOT NULL,
        "costPrice" numeric(10,2) NOT NULL,
        "currentStock" numeric(10,2) NOT NULL DEFAULT 0,
        "stockQuantity" numeric(10,2) NOT NULL DEFAULT 0,
        "reorderLevel" numeric(10,2) NOT NULL DEFAULT 0,
        "supplierId" uuid REFERENCES "suppliers"("id") ON DELETE SET NULL,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Material Requirements table
    await queryRunner.query(`
      CREATE TABLE "material_requirements" (
        "id" SERIAL PRIMARY KEY,
        "productId" uuid REFERENCES "products"("id") ON DELETE CASCADE,
        "materialId" uuid REFERENCES "materials"("id") ON DELETE CASCADE,
        "orderId" varchar NOT NULL,
        "orderItemId" varchar NOT NULL,
        "quantity" numeric(10,2) NOT NULL,
        "wastagePercentage" numeric(10,2) NOT NULL,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Quality Templates table
    await queryRunner.query(`
      CREATE TABLE "quality_templates" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "description" text,
        "type" varchar NOT NULL,
        "inspectionCriteria" jsonb,
        "productId" uuid REFERENCES "products"("id") ON DELETE SET NULL,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Quality Checks table
    await queryRunner.query(`
      CREATE TABLE "quality_checks" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "orderItemId" uuid REFERENCES "order_items"("id") ON DELETE CASCADE,
        "status" varchar NOT NULL DEFAULT 'pending',
        "notes" text,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Inspections table
    await queryRunner.query(`
      CREATE TABLE "inspections" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "qualityCheckId" uuid REFERENCES "quality_checks"("id") ON DELETE CASCADE,
        "results" jsonb NOT NULL,
        "inspector" text NOT NULL,
        "notes" text,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Maintenance Requests table
    await queryRunner.query(`
      CREATE TABLE "maintenance_requests" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "title" varchar NOT NULL,
        "description" text NOT NULL,
        "machineId" uuid REFERENCES "machines"("id") ON DELETE CASCADE,
        "type" varchar NOT NULL,
        "priority" varchar NOT NULL,
        "status" varchar NOT NULL DEFAULT 'pending',
        "scheduledDate" timestamp with time zone,
        "assignedTechnicianId" uuid REFERENCES "users"("id"),
        "notes" text,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Purchase Orders table
    await queryRunner.query(`
      CREATE TABLE "purchase_orders" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "supplierId" uuid REFERENCES "suppliers"("id") ON DELETE CASCADE,
        "orderNumber" varchar UNIQUE NOT NULL,
        "status" varchar NOT NULL DEFAULT 'pending',
        "totalAmount" numeric(10,2) NOT NULL,
        "expectedDelivery" timestamp with time zone,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Purchase Order Items table
    await queryRunner.query(`
      CREATE TABLE "purchase_order_items" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "purchaseOrderId" uuid REFERENCES "purchase_orders"("id") ON DELETE CASCADE,
        "productId" uuid REFERENCES "products"("id") ON DELETE CASCADE,
        "quantity" integer NOT NULL,
        "unitPrice" numeric(10,2) NOT NULL,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Payments table
    await queryRunner.query(`
      CREATE TABLE "payments" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "orderId" uuid REFERENCES "orders"("id") ON DELETE CASCADE,
        "amount" numeric(10,2) NOT NULL,
        "paymentMethod" varchar NOT NULL,
        "status" varchar NOT NULL DEFAULT 'pending',
        "transactionId" varchar,
        "phoneNumber" varchar,
        "refundAmount" numeric(10,2),
        "refundReason" text,
        "refundDate" timestamp with time zone,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Receipts table
    await queryRunner.query(`
      CREATE TABLE "receipts" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "receiptNumber" varchar UNIQUE NOT NULL,
        "amount" numeric(10,2) NOT NULL,
        "description" text,
        "orderId" uuid REFERENCES "orders"("id") ON DELETE CASCADE,
        "paymentId" uuid REFERENCES "payments"("id") ON DELETE CASCADE,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Audit Logs table
    await queryRunner.query(`
      CREATE TABLE "audit_logs" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "userId" uuid REFERENCES "users"("id") ON DELETE SET NULL,
        "action" varchar NOT NULL,
        "entityType" varchar NOT NULL,
        "entityId" varchar,
        "oldValues" jsonb,
        "newValues" jsonb,
        "ipAddress" varchar,
        "userAgent" varchar,
        "createdAt" timestamp with time zone DEFAULT now()
      )
    `);

    // CMS Tables
    await queryRunner.query(`
      CREATE TABLE "pages" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "title" varchar NOT NULL,
        "slug" varchar NOT NULL,
        "content" text NOT NULL,
        "status" varchar NOT NULL DEFAULT 'draft',
        "metadata" jsonb,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    await queryRunner.query(`
      CREATE TABLE "contents" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "type" varchar NOT NULL,
        "key" varchar NOT NULL,
        "value" text NOT NULL,
        "image" varchar,
        "section" varchar,
        "label" varchar,
        "sortOrder" integer DEFAULT 0,
        "metadata" jsonb,
        "isActive" boolean DEFAULT true,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    await queryRunner.query(`
      CREATE TABLE "themes" (
        "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
        "name" varchar NOT NULL,
        "colors" jsonb NOT NULL,
        "typography" jsonb NOT NULL,
        "layout" jsonb NOT NULL,
        "components" jsonb NOT NULL,
        "isActive" boolean DEFAULT false,
        "createdAt" timestamp with time zone DEFAULT now(),
        "updatedAt" timestamp with time zone DEFAULT now()
      )
    `);

    // Quote Requests table
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS "quote_requests" (
        "id" SERIAL PRIMARY KEY,
        "productId" integer,
        "quantity" integer NOT NULL,
        "deliveryDate" character varying,
        "specialRequirements" text,
        "contactName" character varying NOT NULL,
        "contactEmail" character varying NOT NULL,
        "contactPhone" character varying NOT NULL,
        "companyName" character varying,
        "projectType" character varying,
        "urgency" character varying DEFAULT 'normal',
        "budget" character varying,
        "timeline" character varying,
        "additionalNotes" text,
        "preferredContactMethod" character varying DEFAULT 'email',
        "status" character varying DEFAULT 'pending',
        "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
        "updatedAt" TIMESTAMP NOT NULL DEFAULT now()
      )
    `);

    // Tour Bookings table
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS "tour_bookings" (
        "id" SERIAL PRIMARY KEY,
        "contactName" character varying NOT NULL,
        "contactEmail" character varying NOT NULL,
        "contactPhone" character varying NOT NULL,
        "companyName" character varying,
        "jobTitle" character varying,
        "tourDate" character varying NOT NULL,
        "tourTime" character varying NOT NULL,
        "groupSize" integer NOT NULL,
        "tourType" character varying DEFAULT 'general',
        "specialInterests" text,
        "accessibilityNeeds" text,
        "additionalNotes" text,
        "preferredGuide" character varying,
        "language" character varying DEFAULT 'english',
        "status" character varying DEFAULT 'pending',
        "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
        "updatedAt" TIMESTAMP NOT NULL DEFAULT now()
      )
    `);

    // Create indexes for better performance
    await queryRunner.query(`CREATE INDEX "idx_users_email" ON "users"("email")`);
    await queryRunner.query(`CREATE INDEX "idx_users_roleId" ON "users"("roleId")`);
    await queryRunner.query(`CREATE INDEX "idx_customers_email" ON "customers"("email")`);
    await queryRunner.query(`CREATE INDEX "idx_orders_customerId" ON "orders"("customerId")`);
    await queryRunner.query(`CREATE INDEX "idx_orders_status" ON "orders"("status")`);
    await queryRunner.query(`CREATE INDEX "idx_order_items_orderId" ON "order_items"("orderId")`);
    await queryRunner.query(`CREATE INDEX "idx_production_queue_orderId" ON "production_queue"("orderId")`);
    await queryRunner.query(`CREATE INDEX "idx_material_requirements_productId" ON "material_requirements"("productId")`);
    await queryRunner.query(`CREATE INDEX "idx_quality_checks_orderItemId" ON "quality_checks"("orderItemId")`);
    await queryRunner.query(`CREATE INDEX "idx_maintenance_requests_machineId" ON "maintenance_requests"("machineId")`);
    await queryRunner.query(`CREATE INDEX "idx_payments_orderId" ON "payments"("orderId")`);
    await queryRunner.query(`CREATE INDEX "idx_audit_logs_userId" ON "audit_logs"("userId")`);
    await queryRunner.query(`CREATE INDEX "idx_audit_logs_entityType" ON "audit_logs"("entityType")`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop indexes
    await queryRunner.query(`DROP INDEX "idx_audit_logs_entityType"`);
    await queryRunner.query(`DROP INDEX "idx_audit_logs_userId"`);
    await queryRunner.query(`DROP INDEX "idx_payments_orderId"`);
    await queryRunner.query(`DROP INDEX "idx_maintenance_requests_machineId"`);
    await queryRunner.query(`DROP INDEX "idx_quality_checks_orderItemId"`);
    await queryRunner.query(`DROP INDEX "idx_material_requirements_productId"`);
    await queryRunner.query(`DROP INDEX "idx_production_queue_orderId"`);
    await queryRunner.query(`DROP INDEX "idx_order_items_orderId"`);
    await queryRunner.query(`DROP INDEX "idx_orders_status"`);
    await queryRunner.query(`DROP INDEX "idx_orders_customerId"`);
    await queryRunner.query(`DROP INDEX "idx_customers_email"`);
    await queryRunner.query(`DROP INDEX "idx_users_roleId"`);
    await queryRunner.query(`DROP INDEX "idx_users_email"`);

    // Drop tables in reverse order
    await queryRunner.query(`DROP TABLE "tour_bookings"`);
    await queryRunner.query(`DROP TABLE "quote_requests"`);
    await queryRunner.query(`DROP TABLE "themes"`);
    await queryRunner.query(`DROP TABLE "contents"`);
    await queryRunner.query(`DROP TABLE "pages"`);
    await queryRunner.query(`DROP TABLE "audit_logs"`);
    await queryRunner.query(`DROP TABLE "receipts"`);
    await queryRunner.query(`DROP TABLE "payments"`);
    await queryRunner.query(`DROP TABLE "purchase_order_items"`);
    await queryRunner.query(`DROP TABLE "purchase_orders"`);
    await queryRunner.query(`DROP TABLE "suppliers"`);
    await queryRunner.query(`DROP TABLE "maintenance_requests"`);
    await queryRunner.query(`DROP TABLE "inspections"`);
    await queryRunner.query(`DROP TABLE "quality_checks"`);
    await queryRunner.query(`DROP TABLE "quality_templates"`);
    await queryRunner.query(`DROP TABLE "material_requirements"`);
    await queryRunner.query(`DROP TABLE "materials"`);
    await queryRunner.query(`DROP TABLE "production_queue"`);
    await queryRunner.query(`DROP TABLE "machines"`);
    await queryRunner.query(`DROP TABLE "production_lines"`);
    await queryRunner.query(`DROP TABLE "order_items"`);
    await queryRunner.query(`DROP TABLE "orders"`);
    await queryRunner.query(`DROP TABLE "products"`);
    await queryRunner.query(`DROP TABLE "customers"`);
    await queryRunner.query(`DROP TABLE "users"`);
    await queryRunner.query(`DROP TABLE "role_permissions"`);
    await queryRunner.query(`DROP TABLE "permissions"`);
    await queryRunner.query(`DROP TABLE "roles"`);
  }
}