CREATE TABLE "products" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"unit_price" numeric(12, 2) NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"unit" text DEFAULT 'unit' NOT NULL,
	"category" text,
	"tax_rate_id" uuid,
	"is_tracked" boolean DEFAULT false NOT NULL,
	"stock_item_id" uuid,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "products_unit_check" CHECK ("products"."unit" IN ('unit', 'hour', 'day', 'month', 'flat'))
);
--> statement-breakpoint
CREATE TABLE "stock_locations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"code" text,
	"is_default" boolean DEFAULT false NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "inventory_item" (
	"sku_id" uuid PRIMARY KEY NOT NULL,
	"vendor_id" text,
	"quantity_total" integer,
	"quantity_sold" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "stock_movement" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"item_id" uuid NOT NULL,
	"location_id" uuid NOT NULL,
	"kind" text NOT NULL,
	"qty_delta" numeric(19, 4) NOT NULL,
	"unit_cost" numeric(19, 4),
	"cogs_amount" numeric(19, 4),
	"pending_cost" boolean DEFAULT false NOT NULL,
	"holder_ref" text,
	"transfer_group" uuid,
	"occurred_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "stock_position" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"item_id" uuid NOT NULL,
	"location_id" uuid NOT NULL,
	"qty_on_hand" numeric(19, 4) DEFAULT '0' NOT NULL,
	"avg_unit_cost" numeric(19, 4) DEFAULT '0' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "stock_reservation" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"sku_id" uuid NOT NULL,
	"qty" integer NOT NULL,
	"holder_ref" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"consumed_at" timestamp with time zone,
	"released_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "integration_sync_logs" DROP CONSTRAINT "integration_sync_logs_entity_type_check";--> statement-breakpoint
ALTER TABLE "project_milestones" ALTER COLUMN "amount" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "connected_email" text;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "selected_calendar_name" text;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "sync_direction" text DEFAULT 'two_way' NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "sync_task_due_dates" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "sync_manual_events" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "sync_customer_meetings" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "status" text DEFAULT 'active' NOT NULL;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD COLUMN "last_sync_error" text;--> statement-breakpoint
ALTER TABLE "contractors" ADD COLUMN "user_id" uuid;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_min_billable_minutes" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_idle_threshold_minutes" integer DEFAULT 10 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_auto_pause_on_idle" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_standard_hours_per_day" numeric(4, 2) DEFAULT '8.0' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_flag_overtime" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_require_overtime_approval" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_time_enabled" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "mileage_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_magic_link_enabled" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "default_tax_rate" numeric(5, 4) DEFAULT '0.18' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_number_prefix" text DEFAULT 'INV-' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "issue_tax_invoices" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "proforma_number_prefix" text DEFAULT 'PROFORMA-' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "late_fee_type" text DEFAULT 'none' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "late_fee_amount" numeric(10, 2);--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "late_fee_threshold_days" integer DEFAULT 30;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_footer_text" text;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_show_payment_link" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "inventory_method" text DEFAULT 'fifo' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "allow_negative" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "lead_stage_probabilities" jsonb DEFAULT '{"NEW":5,"CONTACTED":15,"QUALIFIED":30,"PROPOSAL":60}'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "hourly_cost" numeric(8, 2);--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancellation_reason" text;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancellation_reason_freetext" text;--> statement-breakpoint
ALTER TABLE "tasks" ADD COLUMN "start_date" date;--> statement-breakpoint
ALTER TABLE "tasks" ADD COLUMN "is_milestone" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "project_milestones" ADD COLUMN "position" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "accepted_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "accepted_by_name" text;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "rejected_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "rejected_reason" text;--> statement-breakpoint
ALTER TABLE "products" ADD CONSTRAINT "products_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "products" ADD CONSTRAINT "products_tax_rate_id_tax_rates_id_fk" FOREIGN KEY ("tax_rate_id") REFERENCES "public"."tax_rates"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_locations" ADD CONSTRAINT "stock_locations_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_movement" ADD CONSTRAINT "stock_movement_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_movement" ADD CONSTRAINT "stock_movement_item_id_inventory_item_sku_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."inventory_item"("sku_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_movement" ADD CONSTRAINT "stock_movement_location_id_stock_locations_id_fk" FOREIGN KEY ("location_id") REFERENCES "public"."stock_locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_position" ADD CONSTRAINT "stock_position_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_position" ADD CONSTRAINT "stock_position_item_id_inventory_item_sku_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."inventory_item"("sku_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_position" ADD CONSTRAINT "stock_position_location_id_stock_locations_id_fk" FOREIGN KEY ("location_id") REFERENCES "public"."stock_locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "stock_reservation" ADD CONSTRAINT "stock_reservation_sku_id_inventory_item_sku_id_fk" FOREIGN KEY ("sku_id") REFERENCES "public"."inventory_item"("sku_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "idx_products_name_tenant" ON "products" USING btree ("tenant_id","name");--> statement-breakpoint
CREATE INDEX "idx_products_tenant_active" ON "products" USING btree ("tenant_id","is_active");--> statement-breakpoint
CREATE INDEX "idx_stock_locations_tenant" ON "stock_locations" USING btree ("tenant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "uq_stock_locations_tenant_code" ON "stock_locations" USING btree ("tenant_id","code");--> statement-breakpoint
CREATE INDEX "idx_stock_movement_tenant_item" ON "stock_movement" USING btree ("tenant_id","item_id");--> statement-breakpoint
CREATE INDEX "idx_stock_movement_occurred_at" ON "stock_movement" USING btree ("tenant_id","occurred_at");--> statement-breakpoint
CREATE INDEX "idx_stock_position_tenant" ON "stock_position" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_stock_position_item" ON "stock_position" USING btree ("tenant_id","item_id");--> statement-breakpoint
CREATE UNIQUE INDEX "uq_stock_position_location" ON "stock_position" USING btree ("tenant_id","item_id","location_id");--> statement-breakpoint
ALTER TABLE "contractors" ADD CONSTRAINT "contractors_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_milestones" ADD CONSTRAINT "project_milestones_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" DROP COLUMN "cancel_reason";--> statement-breakpoint
ALTER TABLE "zync_subscriptions" DROP COLUMN "cancel_feedback";--> statement-breakpoint
ALTER TABLE "zync_subscriptions" DROP COLUMN "cancel_intent_at";--> statement-breakpoint
ALTER TABLE "zync_subscriptions" DROP COLUMN "cancel_at_period_end";--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_sync_direction_check" CHECK ("calendar_connections"."sync_direction" IN ('two_way','read_only','push_only'));--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_status_check" CHECK ("calendar_connections"."status" IN ('active','error','disconnected'));--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_late_fee_type_check" CHECK ("tenant_settings"."late_fee_type" IN ('none', 'flat', 'percentage'));--> statement-breakpoint
ALTER TABLE "integration_sync_logs" ADD CONSTRAINT "integration_sync_logs_entity_type_check" CHECK ("integration_sync_logs"."entity_type" IN ('invoice','credit_note','payment','customer'));
--> statement-breakpoint
-- raw_ddl: drizzle-kit cannot express partial indexes
CREATE UNIQUE INDEX "uq_stock_locations_default_tenant" ON "stock_locations" ("tenant_id") WHERE "is_default" = true;
--> statement-breakpoint
CREATE UNIQUE INDEX "uq_active_holder" ON "stock_reservation" ("sku_id", "holder_ref") WHERE "consumed_at" IS NULL AND "released_at" IS NULL;
--> statement-breakpoint
CREATE INDEX "ix_active_sku" ON "stock_reservation" ("sku_id") WHERE "consumed_at" IS NULL AND "released_at" IS NULL;
--> statement-breakpoint
INSERT INTO "stock_locations" ("tenant_id", "name", "code", "is_default", "is_active")
SELECT "id", 'Main', 'MAIN', true, true
FROM "tenants";
--> statement-breakpoint
INSERT INTO oauth_clients (id, client_id, client_secret_hash, name, redirect_uris, scopes, is_first_party, logo_url)
VALUES
  (
    gen_random_uuid(),
    'zapier_zync',
    encode(sha256('PLACEHOLDER_ZAPIER_SECRET'::bytea), 'hex'),
    'Zapier',
    '["https://zapier.com/dashboard/auth/oauth/return/App1234CLIAPI/"]'::jsonb,
    '["read:invoices","write:invoices","read:customers","write:customers","read:leads","write:leads","read:time","write:time","read:events","read:projects","write:tasks"]'::jsonb,
    false,
    NULL
  ),
  (
    gen_random_uuid(),
    'make_zync',
    encode(sha256('PLACEHOLDER_MAKE_SECRET'::bytea), 'hex'),
    'Make',
    '["https://www.make.com/oauth/cb/app"]'::jsonb,
    '["read:invoices","write:invoices","read:customers","write:customers","read:leads","write:leads","read:time","write:time","read:events","read:projects","write:tasks"]'::jsonb,
    false,
    NULL
  )
ON CONFLICT (client_id) DO UPDATE
  SET redirect_uris = EXCLUDED.redirect_uris,
      scopes = EXCLUDED.scopes,
      name = EXCLUDED.name;
