CREATE TYPE "public"."attachment_kind" AS ENUM('TRADE_CHART', 'STRATEGY_IMAGE');--> statement-breakpoint
CREATE TYPE "public"."portfolio_transaction_type" AS ENUM('buy', 'sell', 'deposit', 'withdrawal', 'order-buy', 'order-sell');--> statement-breakpoint
CREATE TYPE "public"."trade_direction" AS ENUM('LONG', 'SHORT');--> statement-breakpoint
CREATE TYPE "public"."trade_result" AS ENUM('OPEN', 'WIN', 'LOSS', 'BREAKEVEN', 'ORDER');--> statement-breakpoint
CREATE TYPE "public"."trade_type" AS ENUM('Scalp', 'Daytrade', 'Swing');--> statement-breakpoint
CREATE TYPE "public"."trading_cash_transaction_type" AS ENUM('deposit', 'withdrawal', 'conversion');--> statement-breakpoint
CREATE TYPE "public"."user_role" AS ENUM('USER', 'ADMIN');--> statement-breakpoint
CREATE TABLE "sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"token_hash" varchar(64) NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,
	"ip_address" varchar(64),
	"user_agent" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" varchar(320) NOT NULL,
	"password_hash" text NOT NULL,
	"display_name" varchar(120),
	"role" "user_role" DEFAULT 'USER' 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 "attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"owner_id" uuid NOT NULL,
	"kind" "attachment_kind" NOT NULL,
	"entity_id" uuid NOT NULL,
	"object_key" text NOT NULL,
	"original_name" text NOT NULL,
	"mime_type" varchar(160) NOT NULL,
	"byte_size" bigint NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "audit_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"actor_user_id" uuid,
	"action" varchar(120) NOT NULL,
	"entity_type" varchar(120) NOT NULL,
	"entity_id" uuid,
	"metadata" jsonb,
	"ip_address" varchar(64),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "portfolio_transactions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"portfolio_id" uuid NOT NULL,
	"type" "portfolio_transaction_type" NOT NULL,
	"date" date NOT NULL,
	"asset" varchar(30),
	"quantity" numeric(38, 18),
	"price" numeric(38, 18),
	"currency" varchar(12),
	"fee_quantity" numeric(38, 18),
	"fee_asset" varchar(30),
	"cash_amount" numeric(38, 18),
	"cash_currency" varchar(12),
	"note" text,
	"version" integer DEFAULT 1 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 "portfolios" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"owner_id" uuid NOT NULL,
	"name" varchar(120) NOT NULL,
	"note" text,
	"version" integer DEFAULT 1 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 "trade_additions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"trade_id" uuid NOT NULL,
	"date" date NOT NULL,
	"quantity" numeric(38, 18),
	"price" numeric(38, 18),
	"margin" numeric(38, 18),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "trade_partials" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"trade_id" uuid NOT NULL,
	"date" date NOT NULL,
	"quantity" numeric(38, 18),
	"price" numeric(38, 18),
	"pnl" numeric(38, 18),
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "trades" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"account_id" uuid NOT NULL,
	"open_date" date NOT NULL,
	"close_date" date,
	"asset" varchar(30) NOT NULL,
	"direction" "trade_direction" NOT NULL,
	"trade_type" "trade_type" NOT NULL,
	"result" "trade_result" NOT NULL,
	"entry" numeric(38, 18),
	"exit" numeric(38, 18),
	"stop_loss" numeric(38, 18),
	"take_profit" numeric(38, 18),
	"liquidation_price" numeric(38, 18),
	"leverage" numeric(38, 18),
	"capital" numeric(38, 18),
	"quantity" numeric(38, 18),
	"base_quantity" numeric(38, 18),
	"base_entry" numeric(38, 18),
	"base_capital" numeric(38, 18),
	"pnl" numeric(38, 18),
	"setup" text,
	"emotion" text,
	"notes" text,
	"version" integer DEFAULT 1 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 "trading_accounts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"owner_id" uuid NOT NULL,
	"name" varchar(120) NOT NULL,
	"color" varchar(24),
	"exchange" varchar(80),
	"description" text,
	"version" integer DEFAULT 1 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 "trading_cash_transactions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"account_id" uuid NOT NULL,
	"date" date NOT NULL,
	"type" "trading_cash_transaction_type" NOT NULL,
	"amount" numeric(38, 18) NOT NULL,
	"currency" varchar(12) NOT NULL,
	"to_currency" varchar(12),
	"to_amount" numeric(38, 18),
	"counterparty" varchar(160),
	"note" text,
	"version" integer DEFAULT 1 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 "watchlist_items" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"watchlist_id" uuid NOT NULL,
	"coin_id" varchar(160) NOT NULL,
	"symbol" varchar(30) NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "watchlists" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"owner_id" uuid NOT NULL,
	"name" varchar(120) NOT NULL,
	"version" integer DEFAULT 1 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_actor_user_id_users_id_fk" FOREIGN KEY ("actor_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portfolio_transactions" ADD CONSTRAINT "portfolio_transactions_portfolio_id_portfolios_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "public"."portfolios"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portfolios" ADD CONSTRAINT "portfolios_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "trade_additions" ADD CONSTRAINT "trade_additions_trade_id_trades_id_fk" FOREIGN KEY ("trade_id") REFERENCES "public"."trades"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "trade_partials" ADD CONSTRAINT "trade_partials_trade_id_trades_id_fk" FOREIGN KEY ("trade_id") REFERENCES "public"."trades"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "trades" ADD CONSTRAINT "trades_account_id_trading_accounts_id_fk" FOREIGN KEY ("account_id") REFERENCES "public"."trading_accounts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "trading_accounts" ADD CONSTRAINT "trading_accounts_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "trading_cash_transactions" ADD CONSTRAINT "trading_cash_transactions_account_id_trading_accounts_id_fk" FOREIGN KEY ("account_id") REFERENCES "public"."trading_accounts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "watchlist_items" ADD CONSTRAINT "watchlist_items_watchlist_id_watchlists_id_fk" FOREIGN KEY ("watchlist_id") REFERENCES "public"."watchlists"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "watchlists" ADD CONSTRAINT "watchlists_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "sessions_token_hash_unique" ON "sessions" USING btree ("token_hash");--> statement-breakpoint
CREATE INDEX "sessions_user_id_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "sessions_expires_at_idx" ON "sessions" USING btree ("expires_at");--> statement-breakpoint
CREATE UNIQUE INDEX "users_email_unique" ON "users" USING btree ("email");--> statement-breakpoint
CREATE UNIQUE INDEX "attachments_object_key_unique" ON "attachments" USING btree ("object_key");--> statement-breakpoint
CREATE INDEX "attachments_owner_entity_idx" ON "attachments" USING btree ("owner_id","entity_id");--> statement-breakpoint
CREATE INDEX "audit_events_actor_created_idx" ON "audit_events" USING btree ("actor_user_id","created_at");--> statement-breakpoint
CREATE INDEX "audit_events_entity_idx" ON "audit_events" USING btree ("entity_type","entity_id");--> statement-breakpoint
CREATE INDEX "portfolio_transactions_portfolio_date_idx" ON "portfolio_transactions" USING btree ("portfolio_id","date");--> statement-breakpoint
CREATE INDEX "portfolio_transactions_asset_idx" ON "portfolio_transactions" USING btree ("asset");--> statement-breakpoint
CREATE INDEX "portfolios_owner_id_idx" ON "portfolios" USING btree ("owner_id");--> statement-breakpoint
CREATE UNIQUE INDEX "portfolios_owner_name_unique" ON "portfolios" USING btree ("owner_id","name");--> statement-breakpoint
CREATE INDEX "trade_additions_trade_id_idx" ON "trade_additions" USING btree ("trade_id","date");--> statement-breakpoint
CREATE INDEX "trade_partials_trade_id_idx" ON "trade_partials" USING btree ("trade_id","date");--> statement-breakpoint
CREATE INDEX "trades_account_id_idx" ON "trades" USING btree ("account_id");--> statement-breakpoint
CREATE INDEX "trades_account_open_date_idx" ON "trades" USING btree ("account_id","open_date");--> statement-breakpoint
CREATE INDEX "trades_account_result_idx" ON "trades" USING btree ("account_id","result");--> statement-breakpoint
CREATE INDEX "trades_asset_idx" ON "trades" USING btree ("asset");--> statement-breakpoint
CREATE INDEX "trading_accounts_owner_id_idx" ON "trading_accounts" USING btree ("owner_id");--> statement-breakpoint
CREATE UNIQUE INDEX "trading_accounts_owner_name_unique" ON "trading_accounts" USING btree ("owner_id","name");--> statement-breakpoint
CREATE INDEX "trading_cash_transactions_account_id_idx" ON "trading_cash_transactions" USING btree ("account_id","date");--> statement-breakpoint
CREATE UNIQUE INDEX "watchlist_items_watchlist_coin_unique" ON "watchlist_items" USING btree ("watchlist_id","coin_id");--> statement-breakpoint
CREATE INDEX "watchlist_items_watchlist_position_idx" ON "watchlist_items" USING btree ("watchlist_id","position");--> statement-breakpoint
CREATE INDEX "watchlists_owner_id_idx" ON "watchlists" USING btree ("owner_id");--> statement-breakpoint
CREATE UNIQUE INDEX "watchlists_owner_name_unique" ON "watchlists" USING btree ("owner_id","name");